Solved: Re: Tranformation rows with hour to columns

fabiofurlanbr · ‎06-21-2022

Hi guys, I really need some help, I have a data source as follows and I need to transform it to work more quickly with calculations. But I have no idea how to get this result through powerquery , I've already made some attempts but unfortunately they were not successful.

Thank you for your valuable time

I need tranformation the standard datasource to this

Store	Hour_Status_Pickup_Off	Hour_Status_Pickup_On
LOJA WY	13/06/2022 12:05	13/06/2022 12:11
LOJA B	13/06/2022 16:36	13/06/2022 18:32
LOJA E	13/06/2022 20:02	13/06/2022 21:42
LOJA D	14/06/2022 01:05	14/06/2022 01:12
LOJA D	14/06/2022 01:28	14/06/2022 01:29
LOJA D	14/06/2022 02:07	14/06/2022 02:21

This is the standard datasource

StoreStatus - PickupHour_Status_Pickup

LOJA WY	Off	13/06/2022 12:05
LOJA WY	On	13/06/2022 12:11
LOJA B	Off	13/06/2022 16:36
LOJA B	On	13/06/2022 18:32
LOJA E	Off	13/06/2022 20:02
LOJA E	On	13/06/2022 21:42
LOJA D	Off	14/06/2022 01:05
LOJA D	On	14/06/2022 01:12
LOJA D	Off	14/06/2022 01:28
LOJA D	On	14/06/2022 01:29
LOJA D	Off	14/06/2022 02:03
LOJA D	On	14/06/2022 02:07
LOJA D	Off	14/06/2022 02:21
LOJA CB	Off	14/06/2022 05:27
LOJA CB	On	14/06/2022 05:28
LOJA C	Off	14/06/2022 16:10
LOJA C	On	14/06/2022 16:12
LOJA B	Off	14/06/2022 20:21
LOJA B	On	14/06/2022 21:06
LOJA B	Off	14/06/2022 21:19
LOJA B	On	14/06/2022 21:23
LOJA B	Off	14/06/2022 21:57
LOJA B	On	14/06/2022 22:02
LOJA B	Off	15/06/2022 00:05
LOJA U8	Off	15/06/2022 00:35
LOJA B	On	15/06/2022 04:00
LOJA U8	On	15/06/2022 06:32
LOJA A	Off	15/06/2022 10:39
LOJA A	On	15/06/2022 10:48
LOJA D	On	15/06/2022 11:12

tackytechtom · ‎06-21-2022

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/

Did I answer your question❓➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn!

#proudtobeasuperuser

View solution in original post

tackytechtom · ‎06-22-2022

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/

Did I answer your question❓➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn!

#proudtobeasuperuser

View solution in original post

tackytechtom · ‎06-22-2022

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/

Did I answer your question❓➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn!

#proudtobeasuperuser

fabiofurlanbr · ‎06-23-2022

Hi @tackytechtom

My friend, fantastic, it worked perfectly. Thank you very much.

tackytechtom · ‎06-21-2022

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/

Did I answer your question❓➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn!

#proudtobeasuperuser