Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, so i have a table within Power Query with the below structure:
I have some IDs, represanting a case, some subcases and used a concatenation to get a unique ID for each subcase. In the right column I have the status of each subcase.
ID | Subcase | ID_Subcase | Status |
123 | 0 | 123_0 | A |
123 | 1 | 123_1 | B |
123 | 2 | 123_3 | C |
456 | 0 | 456_0 | B |
456 | 1 | 456_1 | C |
456 | 2 | 456_2 | A |
The subcase 0 is the maincase and I would like to add another column called "MainStatus". Each row of the same ID should get the Status from the subcase 0. So my desired output of the transformation should look like this:
ID | Subcase | ID_Subcase | Status | MainStatus |
123 | 0 | 123_0 | A | A |
123 | 1 | 123_1 | B | A |
123 | 2 | 123_3 | C | A |
456 | 0 | 456_0 | B | B |
456 | 1 | 456_1 | C | B |
456 | 2 | 456_2 | A | B |
I think this should be easy but I struggle to find the obsious solution..
Best Regards,
Frederik
Solved! Go to Solution.
Probably a lot of ways to do this.
You can duplicate the table, filter out all rows except subcase = 0. Then merge that with the original table on ID (inner join) OR
a code solution like
Table.AddColumn(#"Changed Type", "Status0", each List.First(
Table.SelectRows(
#"Changed Type",
(r) =>
r[ID] = [ID]
and
r[Subcase] = 0
)[Status],
null
))
Hi @Anonymous ,
Besides merge tables and custom functions, you can also use group by feature and create a custom column to expand them:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMgBiICseRDsqxerAxA2h4iDaCUncCCoOYjuDxU1MzaDmAFlgc5yQxA2h4oZo6o2g4kYQe2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Subcase = _t, ID_Subcase = _t, Status = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Subcase", Int64.Type}, {"ID_Subcase", type text}, {"Status", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Data", each _, type table [ID=nullable number, Subcase=nullable number, ID_Subcase=nullable text, Status=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "MainStatus", each List.First([Data][Status])),
#"Expanded Data" = Table.ExpandTableColumn(#"Added Custom", "Data", {"Subcase", "ID_Subcase", "Status"}, {"Data.Subcase", "Data.ID_Subcase", "Data.Status"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Data",{{"Data.Subcase", "Subcase"}, {"Data.ID_Subcase", "ID_Subcase"}, {"Data.Status", "Status"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"ID", Int64.Type}, {"Subcase", Int64.Type}, {"ID_Subcase", type text}, {"Status", type text}, {"MainStatus", type text}})
in
#"Changed Type1"
Attached a sample file in the below, hopes it could help.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Basically the custom function mentioned by @ HotChilli should work. You can refer my previous sample file with tow workarounds that includes the custom function and group by query.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Besides merge tables and custom functions, you can also use group by feature and create a custom column to expand them:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMgBiICseRDsqxerAxA2h4iDaCUncCCoOYjuDxU1MzaDmAFlgc5yQxA2h4oZo6o2g4kYQe2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Subcase = _t, ID_Subcase = _t, Status = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Subcase", Int64.Type}, {"ID_Subcase", type text}, {"Status", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Data", each _, type table [ID=nullable number, Subcase=nullable number, ID_Subcase=nullable text, Status=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "MainStatus", each List.First([Data][Status])),
#"Expanded Data" = Table.ExpandTableColumn(#"Added Custom", "Data", {"Subcase", "ID_Subcase", "Status"}, {"Data.Subcase", "Data.ID_Subcase", "Data.Status"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Data",{{"Data.Subcase", "Subcase"}, {"Data.ID_Subcase", "ID_Subcase"}, {"Data.Status", "Status"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"ID", Int64.Type}, {"Subcase", Int64.Type}, {"ID_Subcase", type text}, {"Status", type text}, {"MainStatus", type text}})
in
#"Changed Type1"
Attached a sample file in the below, hopes it could help.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Probably a lot of ways to do this.
You can duplicate the table, filter out all rows except subcase = 0. Then merge that with the original table on ID (inner join) OR
a code solution like
Table.AddColumn(#"Changed Type", "Status0", each List.First(
Table.SelectRows(
#"Changed Type",
(r) =>
r[ID] = [ID]
and
r[Subcase] = 0
)[Status],
null
))
I tried to implement your code to my query and reveiced an error:
"Expression.Error: A cyclic reference was encountered during evaluation.
Table.AddColumn(#"myTable",
"MainCase_Status",
each List.First(
Table.SelectRows(
#"myTable",
(r) =>
r[ID] = [ID]
and
r[Subcase] = 0
)[Status],
null
))
Tried to reduce the conditions to just look at one condition, but always the same error.
Any ideas whats I did wrong?
Best regards,
Frederik