Formatting data - Transposing

[Anonymous]

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:

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!

[Anonymous]

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

[Anonymous · Accepted solution]

Hi @Anonymous 

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"