cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FredCeg
Frequent Visitor

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

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. 

IDSubcaseID_SubcaseStatus
1230123_0A
1231123_1B
1232123_3C
4560456_0B
4561456_1C
4562456_2A

 

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:

 

IDSubcaseID_SubcaseStatusMainStatus
1230123_0AA
1231123_1BA
1232123_3CA
4560456_0BB
4561456_1CB
4562456_2AB

 

I think this should be easy but I struggle to find the obsious solution..

 

Best Regards,

Frederik

2 ACCEPTED SOLUTIONS
HotChilli
Super User II
Super User II

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
        ))

View solution in original post

v-yingjl
Community Support
Community Support

Hi @FredCeg ,

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"

first row value.png

 

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.

View solution in original post

4 REPLIES 4
v-yingjl
Community Support
Community Support

Hi @FredCeg ,

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
Community Support
Community Support

Hi @FredCeg ,

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"

first row value.png

 

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.

View solution in original post

HotChilli
Super User II
Super User II

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
        ))

View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors