cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MaxTheMarshall
Frequent Visitor

Formatting data - Transposing

Hi all. We have some rostering software that churns out rosters in a particular format, but we need it to be reformatted and changed around to get it into a different layout completely to be of any use:

MaxTheMarshall_0-1623337625751.png

I imagine using Transpose and DAX will come into it, or maybe even dynamic tables. But does anyone have any pointers on how we'd start approaching reformatting this data?

 

Thanks!

1 ACCEPTED SOLUTION
Vera_33
Solution Sage
Solution Sage

Hi @MaxTheMarshall 

 

If you use Power Query to transform before loaded to model, here is one way, paste in Advanced Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcitKTVHSUTIwNzDQNbQwMEBjK8XqRCt55WfkAdkgIV2QJBpbKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"01/06/2021" = _t, #"02/06/2021" = _t, #"03/06/2021" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Date", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Value", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Start", "End"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Start", type time}, {"End", type time}, {"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ShiftEnd", each if [End]<[Start] then Date.AddDays([Date],1) & [End] else [Date] & [End]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "ShiftStart", each [Date]&[Start]),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Name", "Date", "ShiftStart", "ShiftEnd", "Start", "End"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Start", "End"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns",{"Date", "Name", "ShiftStart", "ShiftEnd"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns1",{{"ShiftStart", type datetime}, {"ShiftEnd", type datetime}})
in
    #"Changed Type1"

 

Vera_33_0-1623375571153.png

 

View solution in original post

2 REPLIES 2
MaxTheMarshall
Frequent Visitor

That's brilliant, thank you VERY VERY much! 😊

Vera_33
Solution Sage
Solution Sage

Hi @MaxTheMarshall 

 

If you use Power Query to transform before loaded to model, here is one way, paste in Advanced Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcitKTVHSUTIwNzDQNbQwMEBjK8XqRCt55WfkAdkgIV2QJBpbKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"01/06/2021" = _t, #"02/06/2021" = _t, #"03/06/2021" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Date", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Value", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Start", "End"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Start", type time}, {"End", type time}, {"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ShiftEnd", each if [End]<[Start] then Date.AddDays([Date],1) & [End] else [Date] & [End]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "ShiftStart", each [Date]&[Start]),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Name", "Date", "ShiftStart", "ShiftEnd", "Start", "End"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Start", "End"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns",{"Date", "Name", "ShiftStart", "ShiftEnd"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns1",{{"ShiftStart", type datetime}, {"ShiftEnd", type datetime}})
in
    #"Changed Type1"

 

Vera_33_0-1623375571153.png

 

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors