空Power Query时上移值 [英] Move up values when null Power Query

问题描述

此刻，我在Excel中有一张大表，希望与动态下拉列表(级联选项)一起使用.根据您在第一个下拉菜单中所做的选择，然后在下一个单元格中，您应该具有一个经过过滤的下拉菜单.此外，对于所选的主要类别，任何选项都是唯一的.

At this moment I have a big table in Excel that I would like to use with dynamic dropdown (cascade options). Depending on the selection you do on the first dropdown, then in the next cell, you should have a filtered drop down menu. Also, any option is unique for the main category selected.

我要做的第一件事是拿起我需要的列并对其进行旋转，使其看起来与此类似(C是列名，V是值.由于任何选项对于任何类别都是唯一的，因此我遇到了问题空值，因为它有很多行):

The first thing I did was to take the columns I need and pivot them so it looks something similar to this (C are the column names and V are the values. Since any option is unique for any category, I have problems with null values since it is a large number of rows):

C1   | C2   | C3
V1   | null | null
V2   | null | null
null | V3   | null
null | null | V4
null | null | V5

此格式不适用于动态下拉菜单，因为它首先显示所有空字段.我的问题是，是否有任何方法可以通过幂查询删除空值，以便我可以将所有值都请求在第一行中，例如:

This format is not working properly for dynamic dropdowns since it is showing all null fields at first. My question is if there is any way to remove null values with power query so I can have all values begging in the first row, such as:

C1   | C2   | C3
V1   | V3   | V4
V2   | null | V5
null | null | null
null | null | null
null | null | null

我已经尝试过填写"或填满"选项，然后删除重复项，但由于重复相同的元素多次而无法正常工作，因此无法正常工作.

I have tried with the "fill down" or "fill up" option and then removing duplicates but is not working since it is repeating the same element so many times that is not useful for a final dropdown menu.

不确定是否有办法做到这一点，因此非常感谢您的帮助或建议.

Not sure if there is a way to accomplish it so any help or suggestion is really appreciated.

提前谢谢！

推荐答案

由于这些列是独立的，因此您可以将每个列变成一个列表，删除空值，然后将它们组合回到表中.

Since the columns are independent, you can turn each one into a list, remove the nulls, then combine them back into a table.

Table.FromColumns(
    {
        List.RemoveNulls(Pivot[C1]),
        List.RemoveNulls(Pivot[C2]),
        List.RemoveNulls(Pivot[C3])
    },
    {"C1","C2","C3"}
)

结果:

如果列数不总是三，但应该应用相同的想法，则可以使此方法更具动态性.

This can be made more dynamic if the number of columns isn't always three but the same idea should apply.

实际上，它比我最初预期的要简单得多，它不受列数及其名称的影响而变得动态:

It's actually simpler than I initially anticipated to make this dynamic, independent of the number of columns and their names:

Table.FromColumns(
    List.Transform(Table.ToColumns(Pivot), List.RemoveNulls), 
    Table.ColumnNames(Pivot)
)

这篇关于空Power Query时上移值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！