Forum Discussion

hylosko's avatar
hylosko
Helper III
4 years ago
Solved

Power query table transformation

Hello!

 

It is any possibility to change table from screen1 to table from screen2 in PQ ?

 

screen1

screen2

 

  • See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpJzMsGUob6hvpGBkZGCoZGVgYGQKTg6AsShmIDPUsoGasTreSKrM8Imz4jkJSeEViPiQmYMjYC6w1F6DXWNzTHZykEgzSFoGjCpQeCDNGdiEMHAsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Vehicle = _t, #"Launch date" = _t, #"KPZ M1" = _t, #"KPZ M2" = _t, #"KPZ M3" = _t, KPZ = _t]),
        #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Vehicle", type text}, {"Launch date", type datetime}, {"KPZ M1", Int64.Type}, {"KPZ M2", type number}, {"KPZ M3", type number}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"KPZ"}),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Name", "Vehicle", "Launch date"}, "Attribute", "Value"),
        #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"Launch date", type date}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Temp", each Table.AddIndexColumn(_,"Index",0,1), type table [Name=text, Vehicle=text, Launch date=nullable date, Value=number, Index=number]}}),
        #"Expanded Temp" = Table.ExpandTableColumn(#"Grouped Rows", "Temp", {"Vehicle", "Launch date", "Value", "Index"}, {"Vehicle", "Launch date", "Value", "Index"}),
        #"Added Custom" = Table.AddColumn(#"Expanded Temp", "Month", each Date.AddMonths([Launch date],[Index])),
        #"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Index"}),
        #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns2",{"Name", "Vehicle", "Launch date", "Month", "Value"}),
        #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Value", "KPZ"}})
    in
        #"Renamed Columns"

     

2 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpJzMsGUob6hvpGBkZGCoZGVgYGQKTg6AsShmIDPUsoGasTreSKrM8Imz4jkJSeEViPiQmYMjYC6w1F6DXWNzTHZykEgzSFoGjCpQeCDNGdiEMHAsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Vehicle = _t, #"Launch date" = _t, #"KPZ M1" = _t, #"KPZ M2" = _t, #"KPZ M3" = _t, KPZ = _t]),
        #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Vehicle", type text}, {"Launch date", type datetime}, {"KPZ M1", Int64.Type}, {"KPZ M2", type number}, {"KPZ M3", type number}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"KPZ"}),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Name", "Vehicle", "Launch date"}, "Attribute", "Value"),
        #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"Launch date", type date}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Temp", each Table.AddIndexColumn(_,"Index",0,1), type table [Name=text, Vehicle=text, Launch date=nullable date, Value=number, Index=number]}}),
        #"Expanded Temp" = Table.ExpandTableColumn(#"Grouped Rows", "Temp", {"Vehicle", "Launch date", "Value", "Index"}, {"Vehicle", "Launch date", "Value", "Index"}),
        #"Added Custom" = Table.AddColumn(#"Expanded Temp", "Month", each Date.AddMonths([Launch date],[Index])),
        #"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Index"}),
        #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns2",{"Name", "Vehicle", "Launch date", "Month", "Value"}),
        #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Value", "KPZ"}})
    in
        #"Renamed Columns"

     

    • hylosko's avatar
      hylosko
      Helper III

      Big Thanks, it working well 🙂