Create table as power query output from Merge excel data

Hi Mohashin_Bhyian 

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