Forum Discussion
CarlosMarin11
3 years agoNew Member
help needed with unpivot columns
Hi Everyone, I´m seeking help and I´ll try to be as visual as possible while I explain with images: Picture #1: This is the file I need to load directly to a model Picture #2: This...
CarlosMarin11
3 years agoNew Member
- jbwtp3 years agoMemorable Member
Somethign like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg82MDBU0lFSitWJVgIxHMEsIyDLCcwyBrKcwSyQUiNkpS5wpa5wpW5wpcbISt3hSj3gSj2VYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Name = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Name", type text}}), trig = (n) => Text.StartsWith(n[Code], "WS"), keep = {"Code", "Name"}, f = (t as table, trigger as function, keepCols as list) => let tbl = Table.Buffer(t), //Consider removal if the code runs slow// Pivot = List.Accumulate(Table.ToRecords(tbl), {}, (a, n)=> if trig(n) then {Record.SelectFields(n, keepCols) & [Nested = {[]}]} & a else {Record.SelectFields(List.First(a), keepCols) & [Nested = List.First(a)[Nested] & {n}]} & List.Skip(a)), Extract = Table.FromRecords(Pivot), Convert = Table.TransformColumns(Extract,{{"Nested", each Table.FromRecords(List.Skip(_))}}) in Convert, out = f(#"Changed Type", trig, keep), #"Expanded Nested" = Table.ExpandTableColumn(out, "Nested", {"Code", "Name"}, {"Nested.Code", "Nested.Name"}) in #"Expanded Nested"Copy f funciton to your query and then call it in the way demonstrated in the out step.Kind regards,
John