Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.