Forum Discussion
Solved
Group data and create columns for weekdays and Time values
Hi All, I need your assitance to shaep the data to the expected format. Current Data Format Expected result the Week days needs to be columns and Time should be like below. ...
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]), FilteredRows = Table.SelectRows(Source, each ([Date] <> "null" and [Date] <> null)), GroupedRows = Table.Group(FilteredRows, {"Collect Point", "Date"}, {{"All", each Table.AddIndexColumn(_, "ID", 1, 1, type text), type table}}), CombinedAll = Table.Combine(GroupedRows[All]), AddedPrefix = Table.TransformColumns(CombinedAll, {{"ID", each "Time" & Text.From(_), type text}}) in AddedPrefix
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