Forum Discussion
Generalizing a table with column reduction
Dear community, actually i want to solve an easy task but i am struggling and hope that someone can help me. I have the following table: Alfred Alfred_1 Alfred_2 Bert Bert_1 Bert_...
Hi,
let
Source = Your_Source,
Name = List.Alternate(Table.ColumnNames(Source),2,1,1),
Data = List.Transform(List.Split(Table.ToColumns(Source),3), each Table.FromColumns(_, {"Start", "End", "Value"})),
#"Data+Name" = Table.FromColumns({Data}&{Name}, {"Data", "Name"}),
Expand = Table.ExpandTableColumn(#"Data+Name", "Data", {"Start", "End", "Value"}, {"Start", "End", "Value"}),
NotNull = Table.SelectRows(Expand, each [Start] <> null)
in
NotNullStéphane
You can also use this version. Just change 2nd step YourSource = Source to your data
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdJdDoQgDATgqxiezab0B/AsxvtfYwVto+wkm0jigPP1QfY9Uf6cDxNLWkcoHjLNOz1IDzqO/fxVrx40HevFM+SZ5sabj/EMeQteIC9eb5iP8QL5ErxCXr2+YT7GK+Rr8AZ5u+uZMB/jDfJt8Mv55qtbGVsG/3NYG7QYW4Ue3y2oed0W/WnW/03GzTY1jy8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Alfred = _t, Alfred_1 = _t, Alfred_2 = _t, Bert = _t, Bert_1 = _t, Bert_2 = _t, Claudia = _t, Claudia_1 = _t, Claudia_2 = _t]),
YourSource = Source,
#"Added Index" = Table.AddIndexColumn(YourSource, "Index", 0, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
Ad_Name = Table.AddColumn(#"Unpivoted Other Columns", "Name", each Text.BeforeDelimiter([Attribute], "_"), type text),
#"Grouped Rows" = Table.Group(Ad_Name, {"Name"}, {{"All", each _, type table [Index=number, Attribute=text, Value=text, Name=text]}, {"Start", each Table.SelectRows(_, (r)=> r[Attribute] = r[Name])[Value], type list}, {"End", each Table.SelectRows(_, (r)=> r[Attribute] = r[Name] & "_1")[Value], type list}, {"Value", each Table.SelectRows(_, (r)=> r[Attribute] = r[Name] & "_2")[Value], type list}}),
Ad_MergedListsToTable = Table.AddColumn(#"Grouped Rows", "MergedListsToTable", each Table.SelectRows(Table.FromColumns({[Start], [End], [Value]}, {"Start", "End", "Value"}), (r)=> Text.Trim(r[Start]) <> null and Text.Trim(r[Start]) <> ""), type table),
#"Removed Other Columns" = Table.SelectColumns(Ad_MergedListsToTable,{"MergedListsToTable", "Name"}),
#"Expanded MergedTables" = Table.ExpandTableColumn(#"Removed Other Columns", "MergedListsToTable", {"Start", "End", "Value"}, {"Start", "End", "Value"})
in
#"Expanded MergedTables"