Forum Discussion

DanFromMontreal's avatar
DanFromMontreal
Icon for Helper IV rankHelper IV
4 years ago
Solved

Pivot table with the latest date

Good morning to all, I'm wanting to pivot a dataset in order to obtain the LATEST result in the [KWh]. I have an idea on on to proceed but cannot figure out to code it. First, I would create an he...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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("hdA9C8MgEAbg/3JzAt4nOmbpUMjUMTi0ELqUEgoZ+u8bQyIoQoVD5R7eQ6cJsL/Mj54IOiA0x9uOW6maE4bYJTHeP3/EsDSEqLFazviWIp05eKFTXNd3Q6gzH7J4lYLrjGF9lkL2DPHhFLd5aYlgTIfI/4H7Oh6DxPWM3E8XccHVIwpALBDjDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"No Project" = _t, Step = _t, KWh = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"No Project", Int64.Type}, {"Step", Int64.Type}, {"KWh", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"No Project", "Step"}, {{"MaxDate", each List.Max([Date]), type nullable date}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"No Project", "Step", "MaxDate"}, #"Changed Type", {"No Project", "Step", "Date"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"KWh"}, {"KWh"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Grouped Rows",{"MaxDate"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Step", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Step", type text}}, "en-US")[Step]), "Step", "KWh", List.Sum)
in
    #"Pivoted Column"
Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

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("hdA9C8MgEAbg/3JzAt4nOmbpUMjUMTi0ELqUEgoZ+u8bQyIoQoVD5R7eQ6cJsL/Mj54IOiA0x9uOW6maE4bYJTHeP3/EsDSEqLFazviWIp05eKFTXNd3Q6gzH7J4lYLrjGF9lkL2DPHhFLd5aYlgTIfI/4H7Oh6DxPWM3E8XccHVIwpALBDjDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"No Project" = _t, Step = _t, KWh = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"No Project", Int64.Type}, {"Step", Int64.Type}, {"KWh", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"No Project", "Step"}, {{"MaxDate", each List.Max([Date]), type nullable date}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"No Project", "Step", "MaxDate"}, #"Changed Type", {"No Project", "Step", "Date"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"KWh"}, {"KWh"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Grouped Rows",{"MaxDate"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Step", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Step", type text}}, "en-US")[Step]), "Step", "KWh", List.Sum)
in
    #"Pivoted Column"