Forum Discussion
Pivot Columns Need to be split as separate columns.
Is there any way we can achive the below output in Power Query..?
maybe this is better
let Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIE4kKlWJ1oJScUnjMKD6TSCIhT4CoRPGcUngsKzxWFBzLFGIhL4PoQPBcELxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CAT = _t, ID = _t, ST = _t]), #"Aggiunta colonna personalizzata" = Table.AddColumn(Origine, "rec", each Record.FromList({[ID],[ST]},{[CAT]&"-ID",[CAT]&"-ST"})), piv = Table.Pivot(#"Aggiunta colonna personalizzata", cat, "CAT", "rec"), cat=List.Distinct(#"Aggiunta colonna personalizzata"[CAT]), n=List.Count(cat), te=List.Accumulate({0..n-1},piv,(s,c)=>Table.ExpandRecordColumn(s, cat{c}, {cat{c}&"-ID", cat{c}&"-ST"})), #"Rimosse colonne" = Table.RemoveColumns(te,{"ID", "ST"}) in #"Rimosse colonne"
2 Replies
check if this is what you are looking for:
let Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIE4kKlWJ1oJScUnjMKD6TSCIhT4CoRPGcUngsKzxWFBzLFGIhL4PoQPBcELxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CAT = _t, ID = _t, ST = _t]), #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"CAT", type text}, {"ID", Int64.Type}, {"ST", type text}}), rr = Table.Group(#"Modificato tipo", {"CAT"}, {{"all", each _[[ID],[ST]], type table [CAT=nullable text, ID=nullable number, ST=nullable text]}}), nrow=Table.RowCount(rr), nj=List.Accumulate({0..nrow-1},rr[all]{0}, (s,c)=>Table.NestedJoin(s,"ID",rr[all]{c},"ID",rr[CAT]{c})), te=List.Accumulate({0..nrow-1},nj,(s,c)=>Table.ExpandTableColumn(s, rr[CAT]{c}, {"ID", "ST"}, {rr[CAT]{c}&".ID", rr[CAT]{c}&".ST"})), #"Rimosse colonne" = Table.RemoveColumns(te,{"ID", "ST"}) in #"Rimosse colonne"maybe this is better
let Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIE4kKlWJ1oJScUnjMKD6TSCIhT4CoRPGcUngsKzxWFBzLFGIhL4PoQPBcELxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CAT = _t, ID = _t, ST = _t]), #"Aggiunta colonna personalizzata" = Table.AddColumn(Origine, "rec", each Record.FromList({[ID],[ST]},{[CAT]&"-ID",[CAT]&"-ST"})), piv = Table.Pivot(#"Aggiunta colonna personalizzata", cat, "CAT", "rec"), cat=List.Distinct(#"Aggiunta colonna personalizzata"[CAT]), n=List.Count(cat), te=List.Accumulate({0..n-1},piv,(s,c)=>Table.ExpandRecordColumn(s, cat{c}, {cat{c}&"-ID", cat{c}&"-ST"})), #"Rimosse colonne" = Table.RemoveColumns(te,{"ID", "ST"}) in #"Rimosse colonne"