Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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"
That's brilliant, thank you VERY VERY much! 😊
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"