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

Hi Dufo,

 

Good morning. Sorry to interuppt you.

I have the hours split for all weekdays but only for friday it is showing time1 and time where it should be time1 ana time2.

 

Due to which in the output power pivot it is showing time1 and time2 in sigle column for friday.

 

 

Will attaxch source file and code

 

let
Source = DHL,
#"Removed Other Columns" = Table.SelectColumns(Source,{"Index", "DateTime"}),
#"Trimmed Text" = Table.TransformColumns(#"Removed Other Columns",{{"DateTime", Text.Trim, type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Trimmed Text", {{"DateTime", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "DateTime"),
#"Cleaned Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"DateTime", Text.Clean, type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Cleaned Text", "DateTime", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.None, false), {"Date", "Time"}),
GroupedRows = Table.Group(#"Split Column by Delimiter1", {"Index", "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