Hello!Can anybody help me to transform a table where total number of columns is not constant? Thanks Input Table:“Key” column is always fixedTotal 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 thisMy task is to obtain a table where every row will have “key” value and two non-NULL values.

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

Extract number values from columns | Microsoft Fabric Community

7 Replies

v-yulgu-msft

Microsoft Employee

9 years ago

Hi Max1,

The summarized steps could be:

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

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

MarcelBeug

Community Champion

9 years ago

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
Regular Visitor
9 years ago
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

Max1
Regular Visitor
9 years ago
Hi v-yulgu-msft,
Thanks a lot for your solution!
It worked fine for me

Have a nice day!
Max
- MarcelBeug
  Community Champion
  9 years ago
  Max1 Why are you going for the long solution and not for the short one?

mechanix85

Helper I

9 years ago

"Таблица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

Helper I

9 years ago

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=

Forum Discussion

Extract number values from columns

7 Replies

Related content

Extracting complicated text from a column

Extract Data from sharepoint

Extract number values from column

Extract number from a string column

DAX - extract numbers from text to a new number column.