Transfer the column value of the "MotherID" to the "DaughterIDs"

[Anonymous]

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

[HotChilli · Accepted 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
        ))

[v-yingjl]

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.

[v-yingjl · Accepted solution]

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.

[HotChilli · Accepted 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
        ))

[Anonymous]

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