Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
joooffice
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
camargos88
Community Champion
Community Champion

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
v-juanli-msft
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
camargos88
Community Champion
Community Champion

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!



Anonymous
Not applicable

 

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

 

 

Anonymous
Not applicable

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors