Group data and create columns for weekdays and Time values

Hi,

1. you sample data doesn't match with result...
2. provide sample data as table or upload it and paste here a link so we can copy it

Hi dufo,

Thanks for your prompt response and i have pasted the data table.

Thanks for your support.

Regards.

Bhaslar

I have shared the sample data with google drive link

bhaskarpbi999, 

Result:

v1 (will work only if you have 2 open hours for each day)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zZXLCsIwEEV/Zeg6atOKtu6kKhZaLVpxoV0EfBBJU2n1/60DImJcSmYzyYQ8DieLu9s5nPvD0GFOVNU3eZFKVQ2sr0Lqdm0yTyAbJ+lyMV0z2Nd7HfVgUouzKKUWDQMeMMgq1YVYH7qQCVVW+thAHOPm11Fs8B2cea4PZXu7viv1HrhTMJs06bItbjhy3Q7vt5UKEh8gUkADKd+Qs4RItCxtp+QsIRItS/mcnCVEomVptiJnCZF+WAqeXS5qeRL/pvFDb2gIEs8miiFF7PN8fpVVHkN+2Och5MeQHPZ5CPkxZIZ9HkJ+DGlhn+fLT/EA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Postal = _t, #"City Country" = _t, #"Collect Point" = _t, Date = _t, Time = _t, Index = _t]),
    TransformCollectPoint = Table.TransformColumns(Source, {{"Collect Point", each Text.Combine(List.Transform(Text.Split(_, "#(lf)"), (x)=> Text.Trim(x, {",", " ", "#(cr)", "#(lf)"})), " | "), type text}} ),
    FilteredRows = Table.SelectRows(TransformCollectPoint, each ([Date] <> "null" and [Date] <> null)),
    Days = #table(type table[Short=text, Long=text], {{"MO", "Monday"}, {"TU", "Tuesday"}, {"WE", "Wednesday"}, {"TH", "Thursday"}, {"FR", "Friday"}, {"SA", "Saturday"}, {"SU", "Sunday"}}),
    DaysFiltered = Table.Buffer(Table.SelectRows(Days, each List.Contains(List.Distinct(FilteredRows[Date]), [Short]))),
    StepBack = FilteredRows,
    PivotedColumn = Table.Pivot(StepBack, List.Distinct(StepBack[Date]), "Date", "Time", each _),
    RenamedColumns = Table.RenameColumns(PivotedColumn, Table.ToRows(DaysFiltered), MissingField.Ignore),
    TransformDayColumns = Table.TransformColumns(RenamedColumns, List.Transform(DaysFiltered[Long], (colName)=>
     { colName, each Text.Combine(_, "||"), type text } )),
    SplitColumnByDelimiter = List.Accumulate( 
     List.Zip( { List.Repeat({"1"}, List.Count(DaysFiltered[Long])), List.Repeat({"2"}, List.Count(DaysFiltered[Long])), DaysFiltered[Long] } ),
     TransformDayColumns,
     (s,c)=> Table.SplitColumn(s, c{2}, Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv), { c{2} & " Open / Close " & Text.From(c{0}), c{2} & " Open / Close " & Text.From(c{1}) })
)
in
    SplitColumnByDelimiter

v2 (will work for any number of open hours for each day)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zZXJCsIwEIZfZeg5atKKbb2JCxZcilY8VA8BFyJpKq2+v3VARIxHyVwmmZDl48vhz3NPiCCMPeYNy+qmLkrrsob1VSrTrI2mM0gHs/lyMV4z2FU7M+zAqJJnWSgjawYiYpCWug2JObQhlboozbGGJMHNr6PY4Ds483kARXO7uWv9HoS3Zy5p5sum8LDPeYtHTSWDFD+RRJcQkughEhFL2YacJUSiZWk7JmcJkWhZyqbkLCESLUuTFTlLiPTDUvTsMlmpk/w3TRD7oSXbfJcolhRxz/P5VU55LPnhnoeQH0tyuOch5MeSGe55CPmxpIV7ni8/+wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Postal = _t, #"City Country" = _t, #"Collect Point" = _t, Date = _t, Time = _t, Index = _t]),
    TransformCollectPoint = Table.TransformColumns(Source, {{"Collect Point", each Text.Combine(List.Transform(Text.Split(_, "#(lf)"), (x)=> Text.Trim(x, {",", " ", "#(cr)", "#(lf)"})), " | "), type text}} ),
    FilteredRows = Table.SelectRows(TransformCollectPoint, each ([Date] <> "null" and [Date] <> null)),
    UppercasedText = Table.TransformColumns(FilteredRows,{{"Date", Text.Upper, type text}}),
    Days = #table(type table[Short=text, Long=text], {{"MO", "Monday"}, {"TU", "Tuesday"}, {"WE", "Wednesday"}, {"TH", "Thursday"}, {"FR", "Friday"}, {"SA", "Saturday"}, {"SU", "Sunday"}}),
    DaysFiltered = Table.Buffer(Table.SelectRows(Days, each List.Contains(List.Distinct(UppercasedText[Date]), [Short]))),
    StepBack = UppercasedText,
    PivotedColumn = Table.Pivot(StepBack, List.Distinct(StepBack[Date]), "Date", "Time", each _),
    RenamedColumns = Table.RenameColumns(PivotedColumn, Table.ToRows(DaysFiltered), MissingField.Ignore),
    // Moze sa stat, ze pre niektory den ma napriklad troje rozne otvaracie hodiny.
    MaxOpenHoursCount = List.Max(
    List.TransformMany( Table.ToColumns(Table.SelectColumns(RenamedColumns, DaysFiltered[Long])),
       each {List.Transform(_, List.Count)},
       (x,y)=> List.Max(y)
)),
    StepBack2 = RenamedColumns,
    TransformDayColumns = Table.TransformColumns(StepBack2, List.Transform(DaysFiltered[Long], (colName)=>
     { colName, each Text.Combine(_, "||"), type text } )),
    SplitColumnByDelimiter = List.Accumulate( 
     DaysFiltered[Long],
     TransformDayColumns,
     (s,c)=> Table.SplitColumn(s, c, Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv), List.Transform({1..MaxOpenHoursCount}, (x)=> c & " Open / Close " & Text.From(x) ))
),
    RemovedEmplyColumns = Table.RemoveColumns(SplitColumnByDelimiter, List.Transform(List.PositionOf(List.Transform(Table.ToColumns(SplitColumnByDelimiter), each if List.Count(List.RemoveNulls(_)) = 0 then true else false), true, Occurrence.All), each Table.ColumnNames(SplitColumnByDelimiter){_}))
in
    RemovedEmplyColumns

Hi Dufo,

Many Thanks for your support and solution. Kudos to you for prompt response thanks a lot.

Regards,

Bhaskar