Forum Discussion

Flixy04's avatar
Flixy04
Regular Visitor
3 years ago
Solved

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
Need Help
  • Ahmedx's avatar
    Ahmedx
    3 years ago

    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
Ahmedx's avatar
Ahmedx
Super User
3 years ago

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