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

Data wrangling a data set structured with information header and two column data below

So I have this report data generated from a system that I would like to wrangle to a good tidy format with power query. 

 

The data set has an information about the export in cell A1.

 

Then row 2-6 contains some informationd data for the series that in column. The timeseries data below is then divided with the first column beeing the actual timestamp following with the value and the status of that value (e.g. "Corrected" or "Calculated"). 

I would like to transform this in to a tidy dataset that has each variable as columns and every observation in the rows.

 

I've attached a spreadsheet with some sample data. In the tab "Base data" the data is the original format. I've added a tab "Wanted output" to show how I would like the data to look. 

 

Since I have multiple reports in this format I would ultimately make this in to a function to import and clean data from a folder structure.

 

Can't seem to attach a file so here is the original data:

 

Hourlist 2019-05-14 00:00 - 2019-05-22 23:00    
NameFacility 1 Facility 2 Facility 3 
Facility number      
Alternative Fac. Nr      
Serie-IDABC1 ABC2 ABC3 
Time formatNormal timeNormal timeNormal time
HourValueStatusValueStatusValueStatus
2019-05-14 00:000,00 0,00 0,00 
2019-05-14 01:000,00 0,00 0,00 
2019-05-14 02:000,00 0,00 0,00 
2019-05-14 03:000,00 0,04 0,04 
2019-05-14 04:000,33 0,49Corrected0,49 
2019-05-14 05:001,89 2,07 2,16 
2019-05-14 06:007,71 7,94 7,98 
2019-05-14 07:0015,35 15,67 14,82 
2019-05-14 08:0021,67 23,27 19,98 
2019-05-14 09:0025,66Calculated27,51 23,51 
2019-05-14 10:0027,32Calculated29,11 13,49Corrected
2019-05-14 11:0027,07 28,59 0,00Corrected
2019-05-14 12:0025,42 26,88 0,00Corrected
2019-05-14 13:0021,42 22,73 0,00Corrected
2019-05-14 14:0016,47 17,44 0,00Corrected
2019-05-14 15:009,30 10,12 0,00Corrected
2019-05-14 16:002,24 2,75 0,00Corrected
2019-05-14 17:000,80 1,13 0,00Corrected
2019-05-14 18:000,52 0,72 0,00Corrected
2019-05-14 19:000,07 0,14 0,00Corrected
2019-05-14 20:000,00 0,00 0,00Corrected

 

Optimal output:

HourValueStatusNameFacility numberAlternative Fac. NrSerie-IDTime formatSource
2019-05-14 00:000,00 Facility 1  ABC1Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:00
2019-05-14 01:000,00 Facility 1  ABC1Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:01
2019-05-14 02:000,00 Facility 1  ABC1Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:02
2019-05-14 03:000,00 Facility 1  ABC1Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:03
2019-05-14 04:000,33 Facility 1  ABC1Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:04
2019-05-14 05:001,89 Facility 1  ABC1Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:05
2019-05-14 06:007,71 Facility 1  ABC1Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:06
2019-05-14 07:0015,35 Facility 1  ABC1Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:07
2019-05-14 08:0021,67 Facility 1  ABC1Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:08
2019-05-14 09:0025,66CalculatedFacility 1  ABC1Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:09
2019-05-14 10:0027,32CalculatedFacility 1  ABC1Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:10
2019-05-14 11:0027,07 Facility 1  ABC1Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:11
2019-05-14 12:0025,42 Facility 1  ABC1Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:12
2019-05-14 13:0021,42 Facility 1  ABC1Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:13
2019-05-14 14:0016,47 Facility 1  ABC1Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:14
2019-05-14 15:009,30 Facility 1  ABC1Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:15
2019-05-14 16:002,24 Facility 1  ABC1Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:16
2019-05-14 17:000,80 Facility 1  ABC1Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:17
2019-05-14 18:000,52 Facility 1  ABC1Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:18
2019-05-14 19:000,07 Facility 1  ABC1Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:19
2019-05-14 20:000,00 Facility 1  ABC1Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:20
2019-05-14 00:000,00 Facility 2  ABC2Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:21
2019-05-14 01:000,00 Facility 2  ABC2Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:22
2019-05-14 02:000,00 Facility 2  ABC2Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:23
2019-05-14 03:000,04 Facility 2  ABC2Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:24
2019-05-14 04:000,49CorrectedFacility 2  ABC2Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:25
2019-05-14 05:002,07 Facility 2  ABC2Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:26
2019-05-14 06:007,94 Facility 2  ABC2Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:27
2019-05-14 07:0015,67 Facility 2  ABC2Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:28
2019-05-14 08:0023,27 Facility 2  ABC2Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:29
2019-05-14 09:0027,51 Facility 2  ABC2Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:30
2019-05-14 10:0029,11 Facility 2  ABC2Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:31
2019-05-14 11:0028,59 Facility 2  ABC2Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:32
2019-05-14 12:0026,88 Facility 2  ABC2Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:33
2019-05-14 13:0022,73 Facility 2  ABC2Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:34
2019-05-14 14:0017,44 Facility 2  ABC2Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:35
2019-05-14 15:0010,12 Facility 2  ABC2Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:36
2019-05-14 16:002,75 Facility 2  ABC2Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:37
2019-05-14 17:001,13 Facility 2  ABC2Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:38
2019-05-14 18:000,72 Facility 2  ABC2Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:39
2019-05-14 19:000,14 Facility 2  ABC2Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:40
2019-05-14 20:000,00 Facility 2  ABC2Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:41
2019-05-14 00:000,00 Facility 3  ABC3Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:42
2019-05-14 01:000,00 Facility 3  ABC3Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:43
2019-05-14 02:000,00 Facility 3  ABC3Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:44
2019-05-14 03:000,04 Facility 3  ABC3Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:45
2019-05-14 04:000,49 Facility 3  ABC3Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:46
2019-05-14 05:002,16 Facility 3  ABC3Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:47
2019-05-14 06:007,98 Facility 3  ABC3Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:48
2019-05-14 07:0014,82 Facility 3  ABC3Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:49
2019-05-14 08:0019,98 Facility 3  ABC3Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:50
2019-05-14 09:0023,51 Facility 3  ABC3Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:51
2019-05-14 10:0013,49CorrectedFacility 3  ABC3Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:52
2019-05-14 11:000,00CorrectedFacility 3  ABC3Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:53
2019-05-14 12:000,00CorrectedFacility 3  ABC3Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:54
2019-05-14 13:000,00CorrectedFacility 3  ABC3Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:55
2019-05-14 14:000,00CorrectedFacility 3  ABC3Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:56
2019-05-14 15:000,00CorrectedFacility 3  ABC3Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:57
2019-05-14 16:000,00CorrectedFacility 3  ABC3Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:58
2019-05-14 17:000,00CorrectedFacility 3  ABC3Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:59
2019-05-14 18:000,00CorrectedFacility 3  ABC3Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:60
2019-05-14 19:000,00CorrectedFacility 3  ABC3Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:61
2019-05-14 20:000,00CorrectedFacility 3  ABC3Normal timeHourlist 2019-05-14 00:00 - 2019-05-22 23:62
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
HotChilli New Contributor
New Contributor

Re: Data wrangling a data set structured with information header and two column data below

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 Facility.PNG

 

There are a few columns missing (they didn't seem to be doing anything). Feel free to add them back in.

2 REPLIES 2
Highlighted
HotChilli New Contributor
New Contributor

Re: Data wrangling a data set structured with information header and two column data below

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 Facility.PNG

 

There are a few columns missing (they didn't seem to be doing anything). Feel free to add them back in.

Flyckten Frequent Visitor
Frequent Visitor

Re: Data wrangling a data set structured with information header and two column data below

This solves the problem by manually wrangling the set. Optimally I would like to do this to a bunch of different files in a folder with different amount of columns. If you feel like taking a bite at a solution for that, check out my other post:

 

https://community.powerbi.com/t5/Power-Query/Merge-alternative-columns-dynamically-used-in-a-functio...

 

 

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 307 members 3,398 guests
Please welcome our newest community members: