Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
16 | |
10 | |
10 | |
8 | |
7 |