Tranformation rows with hour to columns

Hi fabiofurlanbr ,

 

Not sure whether this one here is something for you, but it might get you closer to a solution anyway...

 

 

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZO7CsMwDEV/JXgORJLtPLzl0aUUMpVSQtaM+f+xhkJsyyru4ulwhHyvtk091vtYvd6qVutx+Bd1A21DQFQhObBqrxPozBjEwEyip3W6ZQzX9E5TQG6ShsABZ5iG0JkIWYLGXAxgstVyaRIE/9BQX9TQUNL4P9YFjUe6soaiGOZJhKyjjkN8mE3WmiWPzxOBMWeOkFgLE+dJvDpM4/ME3pxM48MaihrSZY3tChpKChhpbPg/SNr17H9A2kqzIsQ4AO7hTJsczSiNQj9qYMyZIUZscoR8D2L/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Store = _t, Status = _t, PickupHour_Status_Pickup = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store", type text}, {"Status", type text}, {"PickupHour_Status_Pickup", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"PickupHour_Status_Pickup", Order.Ascending}}),
    #"Filtered Rows 1" = Table.SelectRows(#"Sorted Rows", each ([Status] = "On")),
    #"Renamed Columns 1" = Table.RenameColumns(#"Filtered Rows 1",{{"PickupHour_Status_Pickup", "PickupHour_Status_Pickup_On"}}),
    #"Added Index 1" = Table.AddIndexColumn(#"Renamed Columns 1", "Index", 1, 1, Int64.Type),
    #"Filtered Rows 2" = Table.SelectRows(#"Sorted Rows", each ([Status] = "Off")),
    #"Renamed Columns 2" = Table.RenameColumns(#"Filtered Rows 2",{{"PickupHour_Status_Pickup", "PickupHour_Status_Pickup_Off"}}),
    #"Added Index 2" = Table.AddIndexColumn(#"Renamed Columns 2", "Index", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index 2", {"Store", "Index"}, #"Added Index 1", {"Store", "Index"}, "Added Index 2", JoinKind.LeftOuter),
    #"Expanded Added Index 2" = Table.ExpandTableColumn(#"Merged Queries", "Added Index 2", {"PickupHour_Status_Pickup_On"}, {"Added Index 2.PickupHour_Status_Pickup_On"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Index 2",{"Status", "Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Added Index 2.PickupHour_Status_Pickup_On", "PickupHour_Status_Pickup_On"}})
in
    #"Renamed Columns"

Let me know if this helps 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Hi fabiofurlanbr ,

 

Gotcha! Next try here 🙂 

 

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZO7CsMwDEV/JXgORJLtPLzl0aUUMpVSQtaM+f+xhkJsyyru4ulwhHyvtk091vtYvd6qVutx+Bd1A21DQFQhObBqrxPozBjEwEyip3W6ZQzX9E5TQG6ShsABZ5iG0JkIWYLGXAxgstVyaRIE/9BQX9TQUNL4P9YFjUe6soaiGOZJhKyjjkN8mE3WmiWPzxOBMWeOkFgLE+dJvDpM4/ME3pxM48MaihrSZY3tChpKChhpbPg/SNr17H9A2kqzIsQ4AO7hTJsczSiNQj9qYMyZIUZscoR8D2L/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Store = _t, Status = _t, PickupHour_Status_Pickup = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store", type text}, {"Status", type text}, {"PickupHour_Status_Pickup", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"PickupHour_Status_Pickup", Order.Ascending}}),
    #"Filtered Rows 1" = Table.SelectRows(#"Sorted Rows", each ([Status] = "On")),
    #"Renamed Columns 1" = Table.RenameColumns(#"Filtered Rows 1",{{"PickupHour_Status_Pickup", "PickupHour_Status_Pickup_On"}}),
    #"Grouped Rows 1" = Table.Group(#"Renamed Columns 1", {"Store"}, {{"AllData", each InnerGroupAddIndex1(_), type table [Store=nullable text, Status=nullable text, PickupHour_Status_Pickup_On=nullable datetime]}}),
    InnerGroupAddIndex1 = (Table as table) as table =>
    let
        #"Added Index" = Table.AddIndexColumn(Table, "Index", 1, 1, Int64.Type)
    in
        #"Added Index",
    #"Expanded AllData 1" = Table.ExpandTableColumn(#"Grouped Rows 1", "AllData", {"Status", "PickupHour_Status_Pickup_On", "Index"}, {"Status", "PickupHour_Status_Pickup_On", "Index"}),
    #"Filtered Rows 2" = Table.SelectRows(#"Sorted Rows", each ([Status] = "Off")),
    #"Renamed Columns 2" = Table.RenameColumns(#"Filtered Rows 2",{{"PickupHour_Status_Pickup", "PickupHour_Status_Pickup_Off"}}),
    #"Grouped Rows 2" = Table.Group(#"Renamed Columns 2", {"Store"}, {{"AllData", each InnerGroupAddIndex2(_), type table [Store=nullable text, Status=nullable text, PickupHour_Status_Pickup_Off=nullable datetime]}}),
    InnerGroupAddIndex2 = (Table as table) as table =>
    let
        #"Added Index" = Table.AddIndexColumn(Table, "Index", 1, 1, Int64.Type)
    in
        #"Added Index",    
    #"Expanded AllData 2" = Table.ExpandTableColumn(#"Grouped Rows 2", "AllData", {"Status", "PickupHour_Status_Pickup_Off", "Index"}, {"Status", "PickupHour_Status_Pickup_Off", "Index"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded AllData 2", {"Store", "Index"}, #"Expanded AllData 1", {"Store", "Index"}, "Expanded AllData 2", JoinKind.LeftOuter),   
    #"Expanded Added Index 2" = Table.ExpandTableColumn(#"Merged Queries", "Expanded AllData 2", {"PickupHour_Status_Pickup_On"}, {"Expanded AllData 2.PickupHour_Status_Pickup_On"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Index 2",{"Status", "Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Expanded AllData 2.PickupHour_Status_Pickup_On", "PickupHour_Status_Pickup_On"}})
in
    #"Renamed Columns"

 

Let me know if this helps 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/