Forum Discussion

bhaskarpbi999's avatar
bhaskarpbi999
Helper 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
dufoq3's avatar
dufoq3
Community Champion
2 years ago

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

      I have shared the sample data with google drive link . thanks for your support.

      • bhaskarpbi999's avatar
        bhaskarpbi999
        Helper V
        2 years ago

        Hi Dufo,

         

        I need above required format with ID column. thanks for your support