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
1 ACCEPTED SOLUTION
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
PivotedColumnHi 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,
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
