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

[bhaskarpbi999]

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.

Sample data below 

Index	Date	Time
1	MO	09:00-14:00
1	MO	16:00-18:00
1	TU	09:00-14:00
1	TU	16:00-18:00
1	WE	09:00-14:00
1	WE	16:00-18:00
1	TH	09:00-14:00
1	TH	16:00-18:00
1	FR	09:00-14:00
1	FR	16:00-18:00
2	MO	09:00-14:00
2	MO	16:00-18:00
2	TU	09:00-14:00
2	TU	16:00-18:00
2	WE	09:00-14:00
2	WE	16:00-18:00
2	TH	09:00-14:00
2	TH	16:00-18:00
2	FR	09:00-14:00
2	FR	16:00-18:00
3	MO	09:30-14:00
3	MO	18:00-21:00
3	TU	09:30-14:00
3	TU	18:00-21:00
3	WE	09:30-14:00
3	WE	18:00-21:00
3	TH	09:30-14:00
3	TH	18:00-21:00
3	FR	09:30-14:00
3	FR	18:00-21:00
3	SA	09:30-14:00
3	SA	18:00-21:00
4	MO	07:00-22:00
4	TU	07:00-22:00
4	WE	07:00-22:00
4	TH	07:00-22:00
4	FR	07:00-22:00
4	SA	07:00-22:00
4	SU	07:00-22:00
5	MO	09:00-13:30
5	MO	16:30-20:00
5	TU	09:00-13:30
5	TU	16:30-20:00
5	WE	09:00-13:30
5	WE	16:30-20:00
5	TH	09:00-13:30
5	TH	16:30-20:00
5	FR	09:00-13:30
5	FR	16:30-20:00
5	SA	10:00-14:00

Thanks for your help in advance

Regards,

Bhaskar

[dufoq3]

Hi @bhaskarpbi999, different approach:

Result

v1 (empty days included)

let
    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]),
    DayList = List.Buffer({{"MO", "Monday"}, {"TU", "Tuesday"}, {"WE", "Wednesday"}, {"TH", "Thursday"}, {"FR", "Friday"}, {"SA", "Saturday"}, {"SU", "Sunday"}}),
    ReplacedDate = Table.TransformColumns(Source, {{"Date", each 
        [ a = List.Select(DayList, (x)=> List.Contains(x, _)){0}?,
          b = Text.Replace(_, a{0}, a{1})
        ][b], type text }}),

    fnTransform = 
        (myTable as table)=>
        let
            // _Detail = GroupedRows{[Index="1"]}[All],
            _Detail = myTable,
            _ChangedType = Table.TransformColumnTypes(_Detail,{{"Index", Int64.Type}}),
            _Buffered = Table.Buffer(_ChangedType),
            _Accumulated = List.Accumulate(
                List.Transform(DayList, (x)=> x{1}),
                #table(type table[Index=Int64.Type], {{_Buffered{0}?[Index]?}}),
                (state, current)=> [ a = Table.SelectRows(_Buffered, (x)=> x[Date] = current),
                                    b = List.Accumulate( {1..(if Table.IsEmpty(a) then 2 else Table.RowCount(a))}, state,
                                                (innerState, innerCurrent)=> Table.AddColumn(innerState, current & " Open / Close " & Text.From(innerCurrent), (y)=> if Table.IsEmpty(a) then null else a{innerCurrent-1}?[Time]?, type text))
                                ][b] )
        in _Accumulated,

    GroupedRows = Table.Group(ReplacedDate, {"Index"}, {{"fn", fnTransform, type table}}),
    Combined = Table.Combine(GroupedRows[fn])
in
    Combined

v2 (empty days excluded)

let
    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]),
    DayList = List.Buffer({{"MO", "Monday"}, {"TU", "Tuesday"}, {"WE", "Wednesday"}, {"TH", "Thursday"}, {"FR", "Friday"}, {"SA", "Saturday"}, {"SU", "Sunday"}}),
    ReplacedDate = Table.TransformColumns(Source, {{"Date", each 
        [ a = List.Select(DayList, (x)=> List.Contains(x, _)){0}?,
          b = Text.Replace(_, a{0}, a{1})
        ][b], type text }}),
    GroupedRows = Table.Group(ReplacedDate, {"Index", "Date"}, {{"All", each 
        [ a = Table.AddIndexColumn(_, "IndexHelper", 1, 1),
          b = Table.TransformColumnTypes(a,{{"IndexHelper", type text}}), 
          c = Table.CombineColumns(b, {"Date", "IndexHelper"},Combiner.CombineTextByDelimiter(" Open / Close ", QuoteStyle.None),"Open / Close")
        ][c], type table}}),
    Combined = Table.Combine(GroupedRows[All]),
    PivotedColumn = Table.Pivot(Combined, List.Distinct(Combined[#"Open / Close"]), "Open / Close", "Time")
in
    PivotedColumn

[bhaskarpbi999]

Hi DUFO, 

Many thanks for your support.

Regards,

Bhaskar

[dufoq3]

You're welcome 😉

[Ahmedx · Accepted solution]

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"

[bhaskarpbi999]

Hi Ahmed,

Thanks for your supportt. I am using  the above code but i am getting "unknownday" Result.

Sample data in source query DHL_Open time.

Result screenshot which has issue

Code which i am using 

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 = DHL_OpenTime,
#"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"

Many thanks for your support. could you please assist me on this issue,

[Ahmedx]

I can't say why you have it like that, most likely you have unprinted characters there.
check the line, it should be two
to do this, add a column and write

[bhaskarpbi999]

Hi Ahmed,

I have checked the date column values and it was taking 3 characters so i trimmed it the issue is resolved.

Kudos to you . Accepting your solution.

Many thanks to you for extreme support regarding this Post.

Regards,

Bhaskar

[bhaskarpbi999]

Please find sample data in google drive link below

https://docs.google.com/spreadsheets/d/1iLO-g-x_iJRYidOTUx3ZiRJaPpDfIfEY/edit?usp=sharing&ouid=10959...