Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Regards,
Bhaskar
Solved! Go to Solution.
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
Hi Dufo,
Many Thanks for your support and solution. Kudos to you for prompt response thanks a lot.
Regards,
Bhaskar
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
I have shared the sample data with google drive link
Provided link access to every one.
Hi dufo,
Thanks for your prompt response and i have pasted the data table.
Thanks for your support.
Regards.
Bhaslar
Postal | City Country | Collect Point | Date | Time | Index |
11379 | Cortijillos Spain | DHL PALMONES, C/ Dragaminas, 18, Pol. Ind. Palmones II PALMONES 11379 203 m | 1 | ||
11379 | Cortijillos Spain | DHL PALMONES, C/ Dragaminas, 18, Pol. Ind. Palmones II PALMONES 11379 203 m | MO | 09:00-14:00 | 1 |
11379 | Cortijillos Spain | DHL PALMONES, C/ Dragaminas, 18, Pol. Ind. Palmones II PALMONES 11379 203 m | MO | 16:00-18:00 | 1 |
11379 | Cortijillos Spain | DHL PALMONES, C/ Dragaminas, 18, Pol. Ind. Palmones II PALMONES 11379 203 m | TU | 09:00-14:00 | 1 |
11379 | Cortijillos Spain | DHL PALMONES, C/ Dragaminas, 18, Pol. Ind. Palmones II PALMONES 11379 203 m | TU | 16:00-18:00 | 1 |
11379 | Cortijillos Spain | DHL PALMONES, C/ Dragaminas, 18, Pol. Ind. Palmones II PALMONES 11379 203 m | WE | 09:00-14:00 | 1 |
11379 | Cortijillos Spain | DHL PALMONES, C/ Dragaminas, 18, Pol. Ind. Palmones II PALMONES 11379 203 m | WE | 16:00-18:00 | 1 |
11379 | Cortijillos Spain | DHL PALMONES, C/ Dragaminas, 18, Pol. Ind. Palmones II PALMONES 11379 203 m | TH | 09:00-14:00 | 1 |
11379 | Cortijillos Spain | DHL PALMONES, C/ Dragaminas, 18, Pol. Ind. Palmones II PALMONES 11379 203 m | TH | 16:00-18:00 | 1 |
11379 | Cortijillos Spain | DHL PALMONES, C/ Dragaminas, 18, Pol. Ind. Palmones II PALMONES 11379 203 m | FR | 09:00-14:00 | 1 |
11379 | Cortijillos Spain | DHL PALMONES, C/ Dragaminas, 18, Pol. Ind. Palmones II PALMONES 11379 203 m | FR | 16:00-18:00 | 1 |
11380 | Tarifa Spain | DHL PALMONES, C/ Dragaminas, 18, Pol. Ind. Palmones II PALMONES 11379 23927 m | 2 | ||
11380 | Tarifa Spain | DHL PALMONES, C/ Dragaminas, 18, Pol. Ind. Palmones II PALMONES 11379 23927 m | MO | 09:00-14:00 | 2 |
11380 | Tarifa Spain | DHL PALMONES, C/ Dragaminas, 18, Pol. Ind. Palmones II PALMONES 11379 23927 m | MO | 16:00-18:00 | 2 |
11380 | Tarifa Spain | DHL PALMONES, C/ Dragaminas, 18, Pol. Ind. Palmones II PALMONES 11379 23927 m | TU | 09:00-14:00 | 2 |
11380 | Tarifa Spain | DHL PALMONES, C/ Dragaminas, 18, Pol. Ind. Palmones II PALMONES 11379 23927 m | TU | 16:00-18:00 | 2 |
11380 | Tarifa Spain | DHL PALMONES, C/ Dragaminas, 18, Pol. Ind. Palmones II PALMONES 11379 23927 m | WE | 09:00-14:00 | 2 |
11380 | Tarifa Spain | DHL PALMONES, C/ Dragaminas, 18, Pol. Ind. Palmones II PALMONES 11379 23927 m | WE | 16:00-18:00 | 2 |
11380 | Tarifa Spain | DHL PALMONES, C/ Dragaminas, 18, Pol. Ind. Palmones II PALMONES 11379 23927 m | TH | 09:00-14:00 | 2 |
11380 | Tarifa Spain | DHL PALMONES, C/ Dragaminas, 18, Pol. Ind. Palmones II PALMONES 11379 23927 m | TH | 16:00-18:00 | 2 |
11380 | Tarifa Spain | DHL PALMONES, C/ Dragaminas, 18, Pol. Ind. Palmones II PALMONES 11379 23927 m | FR | 09:00-14:00 | 2 |
11380 | Tarifa Spain | DHL PALMONES, C/ Dragaminas, 18, Pol. Ind. Palmones II PALMONES 11379 23927 m | FR | 16:00-18:00 | 2 |
I have shared the sample data with google drive link . thanks for your support.
Hi Dufo,
I need above required format with ID column. thanks for your support
Hi Dufo,
Thanks for your support.
I need your assitance to get ID with time1 and time2 for each weekday which is final solution,
Hi Dufo,
I need your assitance to get ID with time1 and time2 for each weekday which is final solution,
Hi Dufo,
I need your assitance to create custom colum ID with time1 and time2 for each weekday which is final solution highlighted in yellow below scrrenshot
Regards,
Bhaskar
You asked for this result (which I've created already):
Now you are sending a screenshot of SOURCE (not result) and to be honest I'm not sure what do you need. So please, send me a screenshot of FINAL EXPECTED RESULT.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
68 | |
64 | |
27 | |
18 | |
13 |