Forum Discussion
Create table as power query output from Merge excel data
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
Hello 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