Forum Discussion
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. ...
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"
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
Hi DUFO,
Many thanks for your support.
Regards,
Bhaskar
You're welcome 😉