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, I'd like to help you, but data in the link you've provided 

looks totally different compared to this one:

 

So how your data looks like?

 

This query is connected to your Google Drive excel worksheet. I've filtered top 1000 rows to make it faster (it loads 10 519 rows in result). As you can see, it works great. Regarding your issue: There is a problem that you have stored both "times" in Time1 column. Provide data with this issue and I can try to help you.

let
    Source = Excel.Workbook(Web.Contents("https://drive.google.com/uc?export=download&id=1htdH3Pd9GRSPRb5xuLJTh1CA7OhbKasH")),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    RenamedColumns = Table.RenameColumns(Sheet1_Sheet,{{"Column1", "Postal City Country"}, {"Column2", "Collect Point"}, {"Column3", "Date Time"}}),
    #"!!!_FilteredTOP1000Rows_!!!" = Table.FirstN(RenamedColumns, 1000),
    AddedIndex = Table.AddIndexColumn(#"!!!_FilteredTOP1000Rows_!!!", "Index", 0, 1, Int64.Type),
    Ad_DateTimeSplit = Table.AddColumn(AddedIndex, "DateTimeSplit", each List.Select(List.Transform(Text.Split([Date Time], "#(lf)"), (x)=> Text.Remove(x, {" ", "#(cr)"})), (y)=> Text.Length(y) > 1), type list),
    ExpandedDateTimeSplit = Table.ExpandListColumn(Ad_DateTimeSplit, "DateTimeSplit"),
    SplitColumnByDelimiter = Table.SplitColumn(ExpandedDateTimeSplit, "DateTimeSplit", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Date", "Time"}),
    RemovedColumns = Table.RemoveColumns(SplitColumnByDelimiter,{"Date Time"}),
    GroupedRows = Table.Group(RemovedColumns, {"Index", "Date"}, {{"All", each Table.AddIndexColumn(_, "ID", 1, 1, Int64.Type), type table}}),
    CombinedAll = Table.Combine(GroupedRows[All]),
    Transformed = Table.TransformColumns(CombinedAll,
        { {"Collect Point", each Text.Combine(Text.Split(Text.Remove(_, {",", "#(cr)"}), "#(lf)"), ", "), type text},
          {"ID", each "Time" & Text.From(_, "sk-SK"), type text} })
in
    Transformed

 

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    I see that there are some discrepancies (see Check column), but I don't see your source data, so I can't help you...

     

    let
    Source = Excel.Workbook(Web.Contents("https://drive.google.com/uc?export=download&id=1kMgzehh1inILMJ7VA-EuHR36vsv53JcO")),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    PromotedHeaders = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    GroupedRows = Table.Group(PromotedHeaders, {"Index", "Date"}, {{"All", each Table.AddIndexColumn(_, "ID dufoq3", 1, 1, Int64.Type), type table}}),
    CombinedAll = Table.Combine(GroupedRows[All]),
    Ad_Check = Table.AddColumn(CombinedAll, "Check", each [ID]=[ID dufoq3], type logical)
in
    Ad_Check