Extract number values from columns

[Max1]

Hello!

Can anybody help me to transform a table where total number of columns is not constant? 

Thanks

Input Table:

• “Key” column is always fixed
• Total number of other columns will change dynamically (but always a minimum of 3 columns)
• Every row will always have only three values in total: “key” and two more distinct values

Output table should look like this

My task is to obtain a table where every row will have “key” value and two non-NULL values.

[v-yulgu-msft]

Hi @Max1,

The summarized steps could be:

1. Unpivot table: Select [Key] column, then unpivot other columns
    
   
   
2. Add a nested rank column. Please refer to this blog for details about how to add a nested rank column.
   
   
   
3. Pivot table: Select the new rank column, then, click the pivot column option.
   
4. Result
   
   

   let
       Source = Excel.Workbook(File.Contents("C:\Users\xxxxx\Desktop\Sample Data.xlsx"), null, true),
       Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
       #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
       #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Key", type text}, {"bd-2", Int64.Type}, {"cc-2", Int64.Type}, {"cc-1", Int64.Type}, {"bd-1", Int64.Type}, {"cc-3", Int64.Type}, {"bd-3", Int64.Type}, {"cc-5", Int64.Type}, {"bd-5", Int64.Type}}),
       #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Key"}, "Attribute", "Value"),
       #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Key"}, {{"New Column1", each _, type table}}),
   RankFunction = (tabletorank as table) as table =>
        let
         #"SortRows" = Table.Sort(tabletorank,{{"Value", Order.Descending}}),
         #"AddIndex" = Table.AddIndexColumn(#"SortRows", "Rank", 1, 1)
        in
         #"AddIndex",
       #"Added Index2" = Table.TransformColumns(#"Grouped Rows", {"New Column1", each RankFunction(_)}),
       #"Expanded New Column1" = Table.ExpandTableColumn(#"Added Index2", "New Column1", {"Attribute", "Value", "Rank"}, {"New Column1.Attribute", "New Column1.Value", "New Column1.Rank"}),
       #"Removed Columns" = Table.RemoveColumns(#"Expanded New Column1",{"New Column1.Attribute"}),
       #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"New Column1.Rank", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"New Column1.Rank", type text}}, "en-US")[#"New Column1.Rank"]), "New Column1.Rank", "New Column1.Value", List.Sum),
       #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"1", "Column1"}, {"2", "Column2"}})
   in
       #"Renamed Columns"

Best regards,
Yuliana Gu

[Max1]

Hi @v-yulgu-msft,

Thanks a lot for your solution! 

It worked fine for me

Have a nice day!

Max

[MarcelBeug]

@Max1 Why are you going for the long solution and not for the short one?

[MarcelBeug · Accepted solution]

Alternatively you can take the second {1} - 0-based - and third {2} non-null field values.

let
    Source = Table1,
    AddedColumn2 = Table.AddColumn(Source, "column2", each List.Select(Record.FieldValues(_), each _<> null){1}),
    AddedColumn3 = Table.AddColumn(AddedColumn2, "column3", each List.Select(Record.FieldValues(_), each _<> null){2}),
    SelectedColumns = Table.SelectColumns(AddedColumn3,{"Key", "column2", "column3"})
in
    SelectedColumns

[Max1]

Hi @MarcelBeug

Thanks a lot for your solution. That's a shorter and easier way.

Works fine for my table.

Have a nice day!

Max

[mechanix85]

Hi,

Таблица1=

let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Другие столбцы с отмененным свертыванием" = Table.UnpivotOtherColumns(Источник, {"key"}, "Атрибут", "Значение"),
    #"Измененный тип" = Table.TransformColumnTypes(#"Другие столбцы с отмененным свертыванием",{{"Значение", type text}}),
    #"Сгруппированные строки" = Table.Group(#"Измененный тип", {"key"}, {{"Количество", each _, type table}}),
    #"Добавлен пользовательский объект4" = Table.AddColumn(#"Сгруппированные строки", "Custom", each Table.ToList(Table.Transpose(Table.FromList(Table.Column([Количество],"Значение"))),Combiner.CombineTextByDelimiter("|"))),
    #"Развернуть Custom.4" = Table.ExpandListColumn(#"Добавлен пользовательский объект4", "Custom"),
    #"Разделить столбец разделителем" = Table.SplitColumn(#"Развернуть Custom.4","Custom",Splitter.SplitTextByDelimiter("|"),{"Custom.1", "Custom.2", "Custom.3"}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Разделить столбец разделителем",{"Количество"})
in
    #"Удаленные столбцы"

result=

[mechanix85]

Hi

"Таблица1" =

let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Другие столбцы с отмененным свертыванием" = Table.UnpivotOtherColumns(Источник, {"key"}, "Атрибут", "Значение"),
    #"Измененный тип" = Table.TransformColumnTypes(#"Другие столбцы с отмененным свертыванием",{{"Значение", type text}}),
    #"Сгруппированные строки" = Table.Group(#"Измененный тип", {"key"}, {{"Количество", each _, type table}}),
    #"Добавлен пользовательский объект4" = Table.AddColumn(#"Сгруппированные строки", "Custom", each Table.ToList(Table.Transpose(Table.FromList(Table.Column([Количество],"Значение"))),Combiner.CombineTextByDelimiter("|"))),
    #"Развернуть Custom.4" = Table.ExpandListColumn(#"Добавлен пользовательский объект4", "Custom"),
    #"Разделить столбец разделителем" = Table.SplitColumn(#"Развернуть Custom.4","Custom",Splitter.SplitTextByDelimiter("|"),{"Custom.1", "Custom.2", "Custom.3"}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Разделить столбец разделителем",{"Количество"})
in
    #"Удаленные столбцы"

result=