Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello!
Can anybody help me to transform a table where total number of columns is not constant?
Thanks
Input Table:
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.
Solved! Go to 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
Hi @Max1,
The summarized steps could be:
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 Why are you going for the long solution and not for the short one?
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
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
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=
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=
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |