Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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
Solved! Go to 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"
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
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 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
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
Please find sample data in google drive link below
User | Count |
---|---|
25 | |
13 | |
12 | |
11 | |
8 |
User | Count |
---|---|
44 | |
27 | |
21 | |
16 | |
12 |