cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Referencing another row in an power query if then else statement

I have my data as below and am trying to find a way in my query to reference another row but the row isnt always previous or next.

 

I'm trying to complete the Group column where empty fields are populated by the male adult in the same account.

 

ie Jack Bloggs is in account 123 with his parents Joe and Angela. Joe is the male adult in the account and is in group A so I want A to appear in the group column. Joe is 2 rows above Jack. 

 

in Account 125 Bob should show in group B but Bob is the row above Jason (the male adult)

 

in account 126 there is no male adult so the child barbara's group should stay blank

 

I'm relatively new to Power Query but used to if then else statements, its just the referencing that i'm struggling with. I then need to use the group column to further manipulate my data which is why i want to do it in power query.

 

Thanks!

 

Account ID

NameMember TypeGenderGroup 
123Joe BloggsAdultMA
123Angela BloggsAdultFB
123Jack BloggsChildM 
125Bob SmithChildM 
125Jason SmithAdultMB
126Susan LesserAdultFC
126Barbara LesserChildF 
127Claire PinkasAdultFA
127John PinkasChildM 
127Sam PinkasAdultMB
1 ACCEPTED SOLUTION
Super User III
Super User III

Hi @joooffice ,

 

Try this mcode:

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc+9CsMgEADgVxHnLG1pO2sgg7RQyBgyXBpRiVHQ5P2roI0kpYN4J5/303X4dL7gCjPLEdVWCB8SMq56CfczxrivMiJGcA1H14RDC8fgPW2qlkqPqRpK6hp/2AG1s1rkH8PAW/NV5Vy53y3E7erBoAf3nrvdWHXBKLgBHGwwd22Krvf4rkE5jl7KTLDfkxSOWWk29WuHqFqYj6XSCv0H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account ID" = _t, Name = _t, #"Member Type" = _t, Gender = _t, Group = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each if [Member Type] = "Child" then
let _accountID = [Account ID],
_group = Table.SelectRows(Source, each [Account ID] = _accountID
and [Member Type] = "Adult" and [Gender] = "M")[Group]
in
if List.Count(_group) > 0 then _group{0} else null
else null),
#"Merged Columns" = Table.CombineColumns(#"Added Custom",{"Custom", "Group"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Group.1")
in
#"Merged Columns"

 

Capture.PNG



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

4 REPLIES 4
Community Support
Community Support

Hi @joooffice 

If any of the answers slove your problem, could you kindly accept it as a solution to close this case and help the other members find it more quickly?
If not, please feel free to let me know.
 
Best Regards
Maggie
Super User III
Super User III

Hi @joooffice ,

 

Try this mcode:

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc+9CsMgEADgVxHnLG1pO2sgg7RQyBgyXBpRiVHQ5P2roI0kpYN4J5/303X4dL7gCjPLEdVWCB8SMq56CfczxrivMiJGcA1H14RDC8fgPW2qlkqPqRpK6hp/2AG1s1rkH8PAW/NV5Vy53y3E7erBoAf3nrvdWHXBKLgBHGwwd22Krvf4rkE5jl7KTLDfkxSOWWk29WuHqFqYj6XSCv0H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account ID" = _t, Name = _t, #"Member Type" = _t, Gender = _t, Group = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each if [Member Type] = "Child" then
let _accountID = [Account ID],
_group = Table.SelectRows(Source, each [Account ID] = _accountID
and [Member Type] = "Adult" and [Gender] = "M")[Group]
in
if List.Count(_group) > 0 then _group{0} else null
else null),
#"Merged Columns" = Table.CombineColumns(#"Added Custom",{"Custom", "Group"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Group.1")
in
#"Merged Columns"

 

Capture.PNG



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

 

try using this expression to add new/uptodate  Group column

 

 

Table.AddColumn(yourtab, "colPers", (r)=>if r[Member Type]="Child" then try Table.SelectRows(Table.SelectRows(mt,each [Account ID]=r[Account ID]),each [Member Type]="Adult" and [Gender]="M"){0}[Group] otherwise null else r[Group])

 

 

 

This expression transforms the column [Group] in place or, to be more precise, transform the [Group] field of the rows of the table, without the need to create a temporary one to be used to replace the old one

 

 

 

 

= Table.FromRecords(Table.TransformRows(youTab,  (r)=>if r[Member Type]="Child" then r&  [Group=try Table.SelectRows(Table.SelectRows(youTab,each [Account ID]=r[Account ID]),each [Member Type]="Adult" and [Gender]="M"){0}[Group] otherwise r[Group]] else r))

 

 

 

 

This approach is probably not the best from a performance perspective, but ...

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

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

Top Solution Authors
Top Kudoed Authors