1 ACCEPTED SOLUTION
Here's my M language. I hope this is what you want.
First I duplicated the query
On the first query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZRNa8MwDIb/SuhZHZbsxPZuXcfYLr107FJ6yDoPAkkLWTLYv5/T+qOkaTGFhLxW9EiyImezma3Kxsxg9lLuqrrq/jK0i+zcQGMDPxq2sImmfd98mtY5Xt6D76LuTLsvu+rXZJZ7yFa3/demrcz87dkaFk9LX5WVFGWs5L1qTPZ9aJuys7bVIOqsq45bu7Vy1xDi9dAPFX2UdT+8WXdl1/8kGAaWGOo5y+coMsYeGbNvGRwf2RU5pvAuiu6i+BQlxnJMCU9xHlyFto/loW3NrjNf0XRJ5ycaQWlHEzAZJBaTVHGiJEg/ABK0iFJNUtLlyoHnztfqwmdDAYomQXUCCaMzcaAA6msZtQNtlmEjy7Le9XV5aglJyDFGc3oUAd3UWGdOFxE0oI+AfNz0cSQMkWJ/FeS+7e6rX+cp7EX4o0YFKJXK89DEyBNInsq7OcMCRGi8BCFSeTdpGrifbmSAlIq7kSMgn9EWn6fS0h8SFZIDJm9deTqP5crkynU41jIgmNo2SvltndHbfw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Hourlist 2019-05-14 00:00 - 2019-05-22 23:00" = _t, #" " = _t, #" .1" = _t, #" .2" = _t, #" .3" = _t, #"(blank)" = _t, #"(blank).1" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Hourlist 2019-05-14 00:00 - 2019-05-22 23:00", type text}, {" ", type text}, {" .1", type text}, {" .2", type text}, {" .3", type text}, {"(blank)", type text}, {"(blank).1", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",5),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Merged Columns" = Table.CombineColumns(#"Promoted Headers",{"Value", "Status"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Facility1"),
#"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Value_1", "Status_2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Facility2"),
#"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Value_3", "Status_4"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Facility3"),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns2", {"Hour"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Value", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, false), {"Value.1", "Value.2"}),
#"Sorted Rows" = Table.Sort(#"Split Column by Delimiter",{{"Attribute", Order.Ascending}, {"Hour", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1)
in
#"Added Index"On the 2nd query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZRNa8MwDIb/SuhZHZbsxPZuXcfYLr107FJ6yDoPAkkLWTLYv5/T+qOkaTGFhLxW9EiyImezma3Kxsxg9lLuqrrq/jK0i+zcQGMDPxq2sImmfd98mtY5Xt6D76LuTLsvu+rXZJZ7yFa3/demrcz87dkaFk9LX5WVFGWs5L1qTPZ9aJuys7bVIOqsq45bu7Vy1xDi9dAPFX2UdT+8WXdl1/8kGAaWGOo5y+coMsYeGbNvGRwf2RU5pvAuiu6i+BQlxnJMCU9xHlyFto/loW3NrjNf0XRJ5ycaQWlHEzAZJBaTVHGiJEg/ABK0iFJNUtLlyoHnztfqwmdDAYomQXUCCaMzcaAA6msZtQNtlmEjy7Le9XV5aglJyDFGc3oUAd3UWGdOFxE0oI+AfNz0cSQMkWJ/FeS+7e6rX+cp7EX4o0YFKJXK89DEyBNInsq7OcMCRGi8BCFSeTdpGrifbmSAlIq7kSMgn9EWn6fS0h8SFZIDJm9deTqP5crkynU41jIgmNo2SvltndHbfw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Hourlist 2019-05-14 00:00 - 2019-05-22 23:00" = _t, #" " = _t, #" .1" = _t, #" .2" = _t, #" .3" = _t, #"(blank)" = _t, #"(blank).1" = _t]),
#"Demoted Headers" = Table.DemoteHeaders(Source),
#"Changed Type" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}}),
#"Kept First Rows" = Table.FirstN(#"Changed Type",1),
#"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows",{"Column1"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "Column1", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", Int64.Type}})
in
#"Changed Type1"Then I 'merged as new' and on the 3rd query:
let
Source = Table.NestedJoin(Table1, {"Hour"}, #"Table1 (2)", {"Column1.1"}, "Table1 (2)", JoinKind.FullOuter),
#"Expanded Table1 (2)" = Table.ExpandTableColumn(Source, "Table1 (2)", {"Column1.1"}, {"Table1 (2).Column1.1"}),
#"Filled Down" = Table.FillDown(#"Expanded Table1 (2)",{"Table1 (2).Column1.1"}),
#"Reordered Columns" = Table.ReorderColumns(#"Filled Down",{"Hour", "Attribute", "Value.1", "Value.2", "Table1 (2).Column1.1", "Index"}),
#"Removed Top Rows" = Table.Skip(#"Reordered Columns",1),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Top Rows",{{"Index", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.PadStart(Text.From([Index]),2,"0")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns", {{"Custom", type text}}, "en-GB"),{"Table1 (2).Column1.1", "Custom"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
#"Reordered Columns1" = Table.ReorderColumns(#"Merged Columns",{"Hour", "Value.1", "Value.2", "Attribute", "Merged"})
in
#"Reordered Columns1"and that should get
There are a few columns missing (they didn't seem to be doing anything). Feel free to add them back in.
Here's my M language. I hope this is what you want.
First I duplicated the query
On the first query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZRNa8MwDIb/SuhZHZbsxPZuXcfYLr107FJ6yDoPAkkLWTLYv5/T+qOkaTGFhLxW9EiyImezma3Kxsxg9lLuqrrq/jK0i+zcQGMDPxq2sImmfd98mtY5Xt6D76LuTLsvu+rXZJZ7yFa3/demrcz87dkaFk9LX5WVFGWs5L1qTPZ9aJuys7bVIOqsq45bu7Vy1xDi9dAPFX2UdT+8WXdl1/8kGAaWGOo5y+coMsYeGbNvGRwf2RU5pvAuiu6i+BQlxnJMCU9xHlyFto/loW3NrjNf0XRJ5ycaQWlHEzAZJBaTVHGiJEg/ABK0iFJNUtLlyoHnztfqwmdDAYomQXUCCaMzcaAA6msZtQNtlmEjy7Le9XV5aglJyDFGc3oUAd3UWGdOFxE0oI+AfNz0cSQMkWJ/FeS+7e6rX+cp7EX4o0YFKJXK89DEyBNInsq7OcMCRGi8BCFSeTdpGrifbmSAlIq7kSMgn9EWn6fS0h8SFZIDJm9deTqP5crkynU41jIgmNo2SvltndHbfw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Hourlist 2019-05-14 00:00 - 2019-05-22 23:00" = _t, #" " = _t, #" .1" = _t, #" .2" = _t, #" .3" = _t, #"(blank)" = _t, #"(blank).1" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Hourlist 2019-05-14 00:00 - 2019-05-22 23:00", type text}, {" ", type text}, {" .1", type text}, {" .2", type text}, {" .3", type text}, {"(blank)", type text}, {"(blank).1", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",5),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Merged Columns" = Table.CombineColumns(#"Promoted Headers",{"Value", "Status"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Facility1"),
#"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Value_1", "Status_2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Facility2"),
#"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Value_3", "Status_4"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Facility3"),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns2", {"Hour"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Value", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, false), {"Value.1", "Value.2"}),
#"Sorted Rows" = Table.Sort(#"Split Column by Delimiter",{{"Attribute", Order.Ascending}, {"Hour", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1)
in
#"Added Index"On the 2nd query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZRNa8MwDIb/SuhZHZbsxPZuXcfYLr107FJ6yDoPAkkLWTLYv5/T+qOkaTGFhLxW9EiyImezma3Kxsxg9lLuqrrq/jK0i+zcQGMDPxq2sImmfd98mtY5Xt6D76LuTLsvu+rXZJZ7yFa3/demrcz87dkaFk9LX5WVFGWs5L1qTPZ9aJuys7bVIOqsq45bu7Vy1xDi9dAPFX2UdT+8WXdl1/8kGAaWGOo5y+coMsYeGbNvGRwf2RU5pvAuiu6i+BQlxnJMCU9xHlyFto/loW3NrjNf0XRJ5ycaQWlHEzAZJBaTVHGiJEg/ABK0iFJNUtLlyoHnztfqwmdDAYomQXUCCaMzcaAA6msZtQNtlmEjy7Le9XV5aglJyDFGc3oUAd3UWGdOFxE0oI+AfNz0cSQMkWJ/FeS+7e6rX+cp7EX4o0YFKJXK89DEyBNInsq7OcMCRGi8BCFSeTdpGrifbmSAlIq7kSMgn9EWn6fS0h8SFZIDJm9deTqP5crkynU41jIgmNo2SvltndHbfw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Hourlist 2019-05-14 00:00 - 2019-05-22 23:00" = _t, #" " = _t, #" .1" = _t, #" .2" = _t, #" .3" = _t, #"(blank)" = _t, #"(blank).1" = _t]),
#"Demoted Headers" = Table.DemoteHeaders(Source),
#"Changed Type" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}}),
#"Kept First Rows" = Table.FirstN(#"Changed Type",1),
#"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows",{"Column1"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "Column1", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", Int64.Type}})
in
#"Changed Type1"Then I 'merged as new' and on the 3rd query:
let
Source = Table.NestedJoin(Table1, {"Hour"}, #"Table1 (2)", {"Column1.1"}, "Table1 (2)", JoinKind.FullOuter),
#"Expanded Table1 (2)" = Table.ExpandTableColumn(Source, "Table1 (2)", {"Column1.1"}, {"Table1 (2).Column1.1"}),
#"Filled Down" = Table.FillDown(#"Expanded Table1 (2)",{"Table1 (2).Column1.1"}),
#"Reordered Columns" = Table.ReorderColumns(#"Filled Down",{"Hour", "Attribute", "Value.1", "Value.2", "Table1 (2).Column1.1", "Index"}),
#"Removed Top Rows" = Table.Skip(#"Reordered Columns",1),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Top Rows",{{"Index", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.PadStart(Text.From([Index]),2,"0")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns", {{"Custom", type text}}, "en-GB"),{"Table1 (2).Column1.1", "Custom"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
#"Reordered Columns1" = Table.ReorderColumns(#"Merged Columns",{"Hour", "Value.1", "Value.2", "Attribute", "Merged"})
in
#"Reordered Columns1"and that should get
There are a few columns missing (they didn't seem to be doing anything). Feel free to add them back in.
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
