Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
fabiofurlanbr
Frequent Visitor

Tranformation rows with hour to columns

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

StoreHour_Status_Pickup_OffHour_Status_Pickup_On
LOJA WY13/06/2022 12:0513/06/2022 12:11
LOJA B13/06/2022 16:3613/06/2022 18:32
LOJA E13/06/2022 20:0213/06/2022 21:42
LOJA D14/06/2022 01:0514/06/2022 01:12
LOJA D14/06/2022 01:2814/06/2022 01:29
LOJA D14/06/2022 02:0714/06/2022 02:21

 

This is the standard datasource

 

StoreStatus - PickupHour_Status_Pickup

LOJA WYOff13/06/2022 12:05
LOJA WYOn13/06/2022 12:11
LOJA BOff13/06/2022 16:36
LOJA BOn13/06/2022 18:32
LOJA EOff13/06/2022 20:02
LOJA EOn13/06/2022 21:42
LOJA DOff14/06/2022 01:05
LOJA DOn14/06/2022 01:12
LOJA DOff14/06/2022 01:28
LOJA DOn14/06/2022 01:29
LOJA DOff14/06/2022 02:03
LOJA DOn14/06/2022 02:07
LOJA DOff14/06/2022 02:21
LOJA CBOff14/06/2022 05:27
LOJA CBOn14/06/2022 05:28
LOJA COff14/06/2022 16:10
LOJA COn14/06/2022 16:12
LOJA BOff14/06/2022 20:21
LOJA BOn14/06/2022 21:06
LOJA BOff14/06/2022 21:19
LOJA BOn14/06/2022 21:23
LOJA BOff14/06/2022 21:57
LOJA BOn14/06/2022 22:02
LOJA BOff15/06/2022 00:05
LOJA U8Off15/06/2022 00:35
LOJA BOn15/06/2022 04:00
LOJA U8On15/06/2022 06:32
LOJA AOff15/06/2022 10:39
LOJA AOn15/06/2022 10:48
LOJA DOn15/06/2022 11:12
2 ACCEPTED SOLUTIONS
tackytechtom
Super User
Super User

Hi @fabiofurlanbr ,

 

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

tomfox_0-1655841248398.png

 

 

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! linkedIn

#proudtobeasuperuser 

View solution in original post

tackytechtom
Super User
Super User

Hi @fabiofurlanbr ,

 

Gotcha! Next try here 🙂 

tomfox_1-1655915631537.png

 

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! linkedIn

#proudtobeasuperuser 

View solution in original post

4 REPLIES 4
tackytechtom
Super User
Super User

Hi @fabiofurlanbr ,

 

Gotcha! Next try here 🙂 

tomfox_1-1655915631537.png

 

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! linkedIn

#proudtobeasuperuser 

Hi @tackytechtom 

 

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

 

 

tackytechtom
Super User
Super User

Hi @fabiofurlanbr ,

 

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

tomfox_0-1655841248398.png

 

 

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! linkedIn

#proudtobeasuperuser 

Hello @tackytechtom 

 

I appreciate the help, thank you very much.

fantastic, it worked perfectly. Thank you very much.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.