Forum Discussion

bhaskarpbi999's avatar
bhaskarpbi999
Icon for Helper V rankHelper V
2 years ago
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.    ...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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
bhaskarpbi999's avatar
bhaskarpbi999
Icon for Helper V rankHelper V
2 years ago

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

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
2 years ago

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.