Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I need some help restructuring the rows into columns at Edit query transformation level
Based on the case date and case number - I'm trying to create the "Date notified", "Date accepted" and "Date on Scene" columns. The column needs to be added based on the number of rows existing. For example, if 10 "Date accepted" values are available for a case, then all those rows need to be transposed to individual columns for that case date and case num.
Can someone please shed some light? I tried Pivot and transpose, but they are not working. I'm not sure it is possible to do it here.
So, the case date and number need to be considered to get a unique row. The case number will repeat.
10136 on 1/07/2022 has four notifications, none date accepted and DateDate on scene
10387 on 1/07/2022 has two notifications, one DateDate accepted and no date on scene
Current structure
Required
Many thanks
DD
Solved! Go to Solution.
@devika
Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("3ZBLCsMwDAWvYrIORD9Ltq4Scv9rxJQuIgeKaKCLLgxmYIZn7/uCG9hGQLSs8Q7IemWFnWSA8jrH+lcqSWBFnXkGkun0qJmLJScw9qA2x/YDtVlQu9ND9QoYEh2pGDQER70Re1DKvEZUZpfqjWiiVHVajy71vSLQsfa7UqxkfubTpuEeJw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Case Date" = _t, #"Alert Date" = _t, #"Case Num" = _t, #"Date Notified" = _t, #"Date Accepted" = _t, #"Date On Scene" = _t]),
#"Changed Type1" = Table.TransformColumnTypes(Source,{{"Case Date", type date}, {"Alert Date", type date}, {"Date Notified", type datetime}, {"Date Accepted", type datetime}, {"Date On Scene", type datetime}}),
#"Grouped Rows1" =
Table.Group(
#"Changed Type1", {"Case Date", "Alert Date", "Case Num"},
{
{"Count", each
let t = Table.FromValue(Text.Combine(List.Transform(_[Date Notified], Text.From), "|")) ,
t2 = Table.RenameColumns(t,{{"Value", "Date Notified"}}),
t3 = Table.SplitColumn(t2 , "Date Notified", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv))
in
t3
},
{"Count2", each
let t = Table.FromValue(Text.Combine(List.Transform(_[Date Accepted], Text.From), "|")) ,
t2 = Table.RenameColumns(t,{{"Value", "Date Accepted"}}),
t3 = Table.SplitColumn(t2 , "Date Accepted", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv))
in
t3
},
{"Count3", each
let t = Table.FromValue(Text.Combine(List.Transform(_[Date On Scene], Text.From), "|")) ,
t2 = Table.RenameColumns(t,{{"Value", "Date on Scene"}}),
t3 = Table.SplitColumn(t2 , "Date on Scene", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv))
in
t3
}
}
),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows1", "Count", Table.ColumnNames(Table.Combine(#"Grouped Rows1"[Count]) ) ),
#"Expanded Count2" = Table.ExpandTableColumn(#"Expanded Count", "Count2", Table.ColumnNames(Table.Combine(#"Grouped Rows1"[Count2]))),
#"Expanded Count3" = Table.ExpandTableColumn(#"Expanded Count2", "Count3", Table.ColumnNames(Table.Combine(#"Grouped Rows1"[Count3])))
in
#"Expanded Count3"
Result:
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@devika
Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("3ZBLCsMwDAWvYrIORD9Ltq4Scv9rxJQuIgeKaKCLLgxmYIZn7/uCG9hGQLSs8Q7IemWFnWSA8jrH+lcqSWBFnXkGkun0qJmLJScw9qA2x/YDtVlQu9ND9QoYEh2pGDQER70Re1DKvEZUZpfqjWiiVHVajy71vSLQsfa7UqxkfubTpuEeJw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Case Date" = _t, #"Alert Date" = _t, #"Case Num" = _t, #"Date Notified" = _t, #"Date Accepted" = _t, #"Date On Scene" = _t]),
#"Changed Type1" = Table.TransformColumnTypes(Source,{{"Case Date", type date}, {"Alert Date", type date}, {"Date Notified", type datetime}, {"Date Accepted", type datetime}, {"Date On Scene", type datetime}}),
#"Grouped Rows1" =
Table.Group(
#"Changed Type1", {"Case Date", "Alert Date", "Case Num"},
{
{"Count", each
let t = Table.FromValue(Text.Combine(List.Transform(_[Date Notified], Text.From), "|")) ,
t2 = Table.RenameColumns(t,{{"Value", "Date Notified"}}),
t3 = Table.SplitColumn(t2 , "Date Notified", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv))
in
t3
},
{"Count2", each
let t = Table.FromValue(Text.Combine(List.Transform(_[Date Accepted], Text.From), "|")) ,
t2 = Table.RenameColumns(t,{{"Value", "Date Accepted"}}),
t3 = Table.SplitColumn(t2 , "Date Accepted", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv))
in
t3
},
{"Count3", each
let t = Table.FromValue(Text.Combine(List.Transform(_[Date On Scene], Text.From), "|")) ,
t2 = Table.RenameColumns(t,{{"Value", "Date on Scene"}}),
t3 = Table.SplitColumn(t2 , "Date on Scene", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv))
in
t3
}
}
),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows1", "Count", Table.ColumnNames(Table.Combine(#"Grouped Rows1"[Count]) ) ),
#"Expanded Count2" = Table.ExpandTableColumn(#"Expanded Count", "Count2", Table.ColumnNames(Table.Combine(#"Grouped Rows1"[Count2]))),
#"Expanded Count3" = Table.ExpandTableColumn(#"Expanded Count2", "Count3", Table.ColumnNames(Table.Combine(#"Grouped Rows1"[Count3])))
in
#"Expanded Count3"
Result:
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy & @PijushRoy - Really appreciate your time, worked perfectly
Now, I'm working to understand what you have done step by step.
M is complex but same time super fun
@PijushRoy Thanks for your time.
Here is the table format, it looks quite not readable, but if you copy below table to excel it is fine. I'm not sure what is the other ways to show it nicely here.
Current structure
Case Date | Alert Date | Case Num | Date Notified | Date Accepted | Date On Scene |
1/07/2022 | 1/07/2022 | 10136 | 1/07/2022 3:24 | ||
1/07/2022 | 1/07/2022 | 10136 | 1/07/2022 3:24 | ||
1/07/2022 | 1/07/2022 | 10136 | 1/07/2022 3:24 | ||
1/07/2022 | 1/07/2022 | 10136 | 1/07/2022 3:24 | ||
1/07/2022 | 1/07/2022 | 10242 | 1/07/2022 6:33 | 1/07/2022 6:34 | |
1/07/2022 | 1/07/2022 | 10293 | 1/07/2022 7:47 | ||
1/07/2022 | 1/07/2022 | 10319 | 1/07/2022 8:18 | ||
1/07/2022 | 1/07/2022 | 10319 | 1/07/2022 8:18 | ||
1/07/2022 | 1/07/2022 | 10387 | 1/07/2022 9:28 | ||
1/07/2022 | 1/07/2022 | 10387 | 1/07/2022 9:28 | 1/07/2022 9:30 | |
1/07/2022 | 1/07/2022 | 10451 | 1/07/2022 10:16 | 1/07/2022 10:17 | |
1/07/2022 | 1/07/2022 | 10451 | 1/07/2022 10:16 | ||
1/07/2022 | 1/07/2022 | 10464 | 1/07/2022 10:25 | 1/07/2022 10:26 | |
1/07/2022 | 1/07/2022 | 10566 | 1/07/2022 11:45 | 1/07/2022 11:51 | |
1/07/2022 | 1/07/2022 | 10566 | 1/07/2022 11:45 | 1/07/2022 11:57 | |
1/07/2022 | 1/07/2022 | 10566 | 1/07/2022 11:45 | ||
Required format
Case Date | Alert Date | Case Num | Date Notified1 | Date Notified2 | Date Notified3 | Date Notified4 | Date Accepted1 | Date On Scene1 |
1/07/2022 | 1/07/2022 | 10136 | 1/07/2022 3:24 | 1/07/2022 3:24 | 1/07/2022 3:24 | 1/07/2022 3:24 | ||
1/07/2022 | 1/07/2022 | 10242 | 1/07/2022 6:33 | 1/07/2022 6:34 | ||||
1/07/2022 | 1/07/2022 | 10293 | 1/07/2022 7:47 | |||||
1/07/2022 | 1/07/2022 | 10319 | 1/07/2022 8:18 | 1/07/2022 8:18 | ||||
1/07/2022 | 1/07/2022 | 10387 | 1/07/2022 9:28 | 1/07/2022 9:28 | 1/07/2022 9:30 | |||
1/07/2022 | 1/07/2022 | 10451 | 1/07/2022 10:16 | 1/07/2022 10:16 | 1/07/2022 10:17 | |||
1/07/2022 | 1/07/2022 | 10464 | 1/07/2022 10:25 | 1/07/2022 10:26 | ||||
1/07/2022 | 1/07/2022 | 10566 | 1/07/2022 11:45 | 1/07/2022 11:45 | 1/07/2022 11:45 | 1/07/2022 11:57 | 1/07/2022 11:51 |