Forum Discussion

bhaskarpbi999's avatar
bhaskarpbi999
Helper V
2 years ago
Solved

Split Weekday time values in to differetn columns for each unque Index ID

Hi Team, I have data like below where each unique index has 7 weedkdays with 2 time values for each day.   I need output like below where each time value should be in different columss.   ...
Need Help
Show and Tell
  • Ahmedx's avatar
    Ahmedx
    2 years ago

    pls try this

    let

    DayName = (shortDay as text) as text =>
      [
        days = [
            MO = "Monday",
            TU = "Tuesday",
            WE = "Wednesday",
            TH = "Thursday",
            FR = "Friday",
            SA = "Saturday",
            SU = "Sunday"
        ],
        fullDayName = Record.FieldOrDefault(days, Text.Upper(shortDay), "Unknown day")
      ][fullDayName],
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZO7DoMwDEX/JTNIiaHPrQOVl6pSH+qA+P/**bleep**QryEl8lyyHI3OCWdeQwhAez3zEyzXGMc35DNtgQDoKOFvw+QJDgGf8FmAIcGcwmsHAuL+AIaA1CJUTKidUTqicUDmhckLlhMoJlRMqn0z5ZI0dyLMjJQtKeWdouWOU8s7Qcm8GoxkMjFLeGVruGO8bMAS0xrzf1UkAWaBX4gAt9wwGQDscoK/rAW/4oVnqKWfWIC9DLqdoDbvUlVGWujPsUldGWep+BqMZDAy71JVRlroz9AtG8xtsfw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Date = _t, Time = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each DayName([Date])& " Open/Close"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Date"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Date"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Index", "Date"}, {{"tmp", 
(x)=> [ a = Table.AddIndexColumn(x, "Id", 1, 1, Int64.Type),
b = Table.CombineColumns(Table.TransformColumnTypes(a, {{"Id", type text}}),{"Date", "Id"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
c = Table.Pivot(b, List.Distinct(b[Merged]), "Merged", "Time")
][c]

}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Index"}, {{"final", 
(x)=> [
    a=  Table.Combine(x[tmp]),
    b = Table.FirstN( Table.FillUp(a,Table.ColumnNames(a)),1)][b]

}}),
    #"Removed Other Columns" = Table.Combine( Table.SelectColumns(#"Grouped Rows1",{"final"})[final])
in
    #"Removed Other Columns"