Create table as power query output from Merge excel data

[Mohashin_Bhyian]

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. 

[wdx223_Daniel]

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.

[Mohashin_Bhyian]

my bad luck 😞 Then I need to solve this with VBA. 

[Mohashin_Bhyian]

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?

[Anonymous · Accepted solution]

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

[wdx223_Daniel]

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
    Custom5