Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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=
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |