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
@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:
@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:
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
