Forum Discussion
Helper IReferencing 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 | Name | Member Type | Gender | Group |
| 123 | Joe Bloggs | Adult | M | A |
| 123 | Angela Bloggs | Adult | F | B |
| 123 | Jack Bloggs | Child | M | |
| 125 | Bob Smith | Child | M | |
| 125 | Jason Smith | Adult | M | B |
| 126 | Susan Lesser | Adult | F | C |
| 126 | Barbara Lesser | Child | F | |
| 127 | Claire Pinkas | Adult | F | A |
| 127 | John Pinkas | Child | M | |
| 127 | Sam Pinkas | Adult | M | B |
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"
4 Replies
camargos88Community 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"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 ...
v-juanli-msftCommunity 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 RegardsMaggie
