Forum Discussion

Baskar's avatar
Baskar
Resident Rockstar
8 years ago
Solved

Transpose column instead of rows in Power Query

Thanks Advanced !!!

 

Input Data

Expected O/P.

Have to transpose the last two column into multiple columns. 

Output Data

 

ImkeF

  • ImkeF's avatar
    ImkeF
    8 years ago

    Please paste this code into the advanced editor and follow the steps:

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZBPC8IwDMW/Sijsto620rEeh04dosj8c5k7bDPoQDZou+9v610YiLvkvSSQX3hlSbLaWNQ9pG2LxpCQFBtXuOSUO91T7uW42roqGF1jQ3ni/MJP81N6yHfOsYgJJ4rLSPkT6TmD5aW4Zv5UQqrw3xw69nXzQrADPNCCfSIkLLiNjIlYyQDu42ena4ug0VjdtbYbehN5RDzDg66RbBpHTOF8TVvNAJma2E8QH1f1Bg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CONSOLE = _t, SITE_CALL_NAME = _t, MTRB_SHORT_NAME = _t, METER_SHORT_NAME = _t, COMMODITY_ID = _t, TEST_DATE = _t, MF_COUNT = _t, MODIFIED_BY = _t, LINEARITY_DEVIATION_PERC = _t, AVG_of_DENSITY = _t, COMMENTS = _t, FLOW_MAX_PERC = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"CONSOLE", type text}, {"SITE_CALL_NAME", type text}, {"MTRB_SHORT_NAME", type text}, {"METER_SHORT_NAME", type text}, {"COMMODITY_ID", type text}, {"TEST_DATE", type date}, {"MF_COUNT", Int64.Type}, {"MODIFIED_BY", type text}, {"LINEARITY_DEVIATION_PERC", type number}, {"AVG_of_DENSITY", type number}, {"COMMENTS", type text}, {"FLOW_MAX_PERC", Int64.Type}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"AVG_of_DENSITY", "COMMENTS"}),
        #"Grouped Rows" = Table.Group(#"Removed Columns", {"SITE_CALL_NAME", "MTRB_SHORT_NAME", "METER_SHORT_NAME", "COMMODITY_ID", "TEST_DATE", "MF_COUNT", "LINEARITY_DEVIATION_PERC"}, {{"All", each _, type table}, {"CountRows", each Table.RowCount(_), type number}}),
        AddTranspose = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Transpose(Table.DemoteHeaders(Table.SelectColumns([All], {"MODIFIED_BY", "FLOW_MAX_PERC"})))),
        DynamicExpand = Table.ExpandTableColumn(AddTranspose, "Custom", List.Transform({1..List.Max(AddTranspose[CountRows])}, each "Column"&Text.From(_))),
        Cleanup = Table.RemoveColumns(DynamicExpand,{"All", "CountRows"})
    in
        Cleanup

     

7 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion

    Why? Your data has the perfect shape for the data model.

     

    1) Group on all columns that shall not be transposed and select "All". That will return partitions of you table

    2) Remove all columns from those tables that shall not be transposed from the partitions

    3) Demote headers and transpose the partitions

    • Baskar's avatar
      Baskar
      Resident Rockstar

      Soory ImkeF i can't able to understand, can u explain me once again .

      • ImkeF's avatar
        ImkeF
        Community Champion

        Hi Baskar,

        please upload sample data, that would probably be easier for me than writing long texts.