Forum Discussion
Flixy04
3 years agoRegular Visitor
Transformation in Power Query
Hello together,
does somebody now how i can transform my table from the above view to the bottom view in PowerQuery Editor?
Best Regards
Flixy04
plse try this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQAA0MlHSUfEKFraqAUq4MqbgQkUIWNYMqBfAxxY5AxaBLGMHOMTDHFwRrQJExgEsZYxMEOBUvEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BLG = _t, LORT = _t, EUR = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"BLG", type text}, {"LORT", type text}, {"EUR", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"BLG"}, {{"Count", ( t ) => { t{0}[BLG] } & t[LORT] & t[EUR], type list }}), Custom1 = List.Sort ( #"Grouped Rows"[Count], ( x ) => - List.Count ( x ) ), Custom2 = Table.FromList ( Custom1, ( x ) => x, { "BLG"} & List.Transform ( { 1 .. List.Count ( Custom1{0} ) - 1 }, ( y ) => "Column" & Text.From ( y ) ) ) in Custom2
1 Reply
- AhmedxSuper User
plse try this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQAA0MlHSUfEKFraqAUq4MqbgQkUIWNYMqBfAxxY5AxaBLGMHOMTDHFwRrQJExgEsZYxMEOBUvEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BLG = _t, LORT = _t, EUR = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"BLG", type text}, {"LORT", type text}, {"EUR", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"BLG"}, {{"Count", ( t ) => { t{0}[BLG] } & t[LORT] & t[EUR], type list }}), Custom1 = List.Sort ( #"Grouped Rows"[Count], ( x ) => - List.Count ( x ) ), Custom2 = Table.FromList ( Custom1, ( x ) => x, { "BLG"} & List.Transform ( { 1 .. List.Count ( Custom1{0} ) - 1 }, ( y ) => "Column" & Text.From ( y ) ) ) in Custom2