Forum Discussion
Create table as power query output from Merge excel data
Hello my dear Expert,
I have a excel data as below. There are many cells are merged. Now I want to unpivot this data.
File download link- Create table as power query output from Merge excel data.xlsx
Input Excel Data:-
Here, you can see Alex started a class "Childcare" at 2.01pm and ended 5.00pm. Means this class is 03hours class.
Next class is started at 5.01pm and ended at 5.30pm. Means- duration is 30min.
Output:
Summery will be created from start to end class time. I am looking for below output. Can you please kindly help on this?
Many thanks.
You can change the format of the excel like the follwing . if the value is the same at different time field, you can add a space between the characters.
Then you can refer to the following code to advanced editor in power query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rU+xDoIwFPyVhpkB0LB0QiZNMESMDshQHy+0WmkCksjf20KQNDIZk0v67q6vd81zJ1G14zqRxJc+Rtx9GnMhSwKswT/ygRbulLljDSMnVZkbK5qBwBrw02DmFplhHjp2RotaIS1L7wQ0YcCtyV464BUBmJ4yBLIm+4huhJKq6pelEcbwyTalKe9bAe2yNCWdsTRJ7DZqaSNIqNs8+aXzvCCcf/WLo3N9UzHm+PimQ4XiDQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Day = _t, Teacher = _t, #"1:00pm-1:30pm" = _t, #"1:31pm-2:00pm" = _t, #"2:01pm-2:30pm" = _t, #"2:31pm-3:00pm" = _t, #"3:01pm-4:00pm" = _t, #"4:01pm-4:30pm" = _t, #"4:31pm-5:00pm" = _t, #"5:01pm-5:30pm" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Day", type text}, {"Teacher", type text}, {"1:00pm-1:30pm", type text}, {"1:31pm-2:00pm", type text}, {"2:01pm-2:30pm", type text}, {"2:31pm-3:00pm", type text}, {"3:01pm-4:00pm", type text}, {"4:01pm-4:30pm", type text}, {"4:31pm-5:00pm", type text}, {"5:01pm-5:30pm", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Day", "Teacher"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type time}, {"Attribute.2", type time}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Value", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Value.1", "Value.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Value.1", type text}, {"Value.2", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type2", {"Day", "Teacher", "Value.1", "Value.2"}, {{"Min", each List.Min([Attribute.1]), type nullable time}, {"Max", each List.Max([Attribute.2]), type nullable time}}), #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Value.2] <> null)), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Filtered Rows", {{"Min", type text}, {"Max", type text}}, "en-US"),{"Min", "Max"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"), #"Reordered Columns" = Table.ReorderColumns(#"Merged Columns",{"Day", "Teacher", "Merged", "Value.1", "Value.2"}), #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns"," ","",Replacer.ReplaceText,{"Value.2"}) in #"Replaced Value"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
5 Replies
import your data without headers, then try this code.
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Custom1 = Table.Transpose(Source), Custom2 = Table.FirstN(Custom1,2)&Table.FillDown(Table.Skip(Custom1,2),Table.ColumnNames(Custom1)), Custom3 = Table.PromoteHeaders(Table.Transpose(Custom2)), Custom4 = Table.UnpivotOtherColumns(Custom3,{"Day","Teacher"},"Time","V"), Custom5 = Table.SplitColumn(Custom4,"V",each Text.Split(_,";"),{"V1","V2"}) in Custom5Hello wdx223_Daniel , Thanks for your response as always. The M-code is actullly fill down all blank cells and giving the result as per my expactation.
Sorry that My question is not clear. I updated the question now.
This is a Class schedule data. So, If you look at the Class duration in my output table, then you can understand what I am looking for.Input:
Here, you can see Alex started a class "Childcare" at 2.01pm and ended 5.00pm. Means this class is 03hours class.Next class is started at 5.01pm and ended at 5.30pm. Means- duration is 30min.
Output:
Summery will be created from start to end class time. I am looking for below output. Can you please kindly help on this?You can change the format of the excel like the follwing . if the value is the same at different time field, you can add a space between the characters.
Then you can refer to the following code to advanced editor in power query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rU+xDoIwFPyVhpkB0LB0QiZNMESMDshQHy+0WmkCksjf20KQNDIZk0v67q6vd81zJ1G14zqRxJc+Rtx9GnMhSwKswT/ygRbulLljDSMnVZkbK5qBwBrw02DmFplhHjp2RotaIS1L7wQ0YcCtyV464BUBmJ4yBLIm+4huhJKq6pelEcbwyTalKe9bAe2yNCWdsTRJ7DZqaSNIqNs8+aXzvCCcf/WLo3N9UzHm+PimQ4XiDQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Day = _t, Teacher = _t, #"1:00pm-1:30pm" = _t, #"1:31pm-2:00pm" = _t, #"2:01pm-2:30pm" = _t, #"2:31pm-3:00pm" = _t, #"3:01pm-4:00pm" = _t, #"4:01pm-4:30pm" = _t, #"4:31pm-5:00pm" = _t, #"5:01pm-5:30pm" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Day", type text}, {"Teacher", type text}, {"1:00pm-1:30pm", type text}, {"1:31pm-2:00pm", type text}, {"2:01pm-2:30pm", type text}, {"2:31pm-3:00pm", type text}, {"3:01pm-4:00pm", type text}, {"4:01pm-4:30pm", type text}, {"4:31pm-5:00pm", type text}, {"5:01pm-5:30pm", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Day", "Teacher"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type time}, {"Attribute.2", type time}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Value", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Value.1", "Value.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Value.1", type text}, {"Value.2", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type2", {"Day", "Teacher", "Value.1", "Value.2"}, {{"Min", each List.Min([Attribute.1]), type nullable time}, {"Max", each List.Max([Attribute.2]), type nullable time}}), #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Value.2] <> null)), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Filtered Rows", {{"Min", type text}, {"Max", type text}}, "en-US"),{"Min", "Max"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"), #"Reordered Columns" = Table.ReorderColumns(#"Merged Columns",{"Day", "Teacher", "Merged", "Value.1", "Value.2"}), #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns"," ","",Replacer.ReplaceText,{"Value.2"}) in #"Replaced Value"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Power Query can not identify the merge area of the cells.
So, the Row #4 of Rebecca, the first 4 periods will be assigned to Sec 4 NA. the same as last 4 periods of the row.
my bad luck 😞 Then I need to solve this with VBA.
