Forum Discussion
Last non blank in Power Query
Hi,
I'm struggling with one of the Query tasks - I need to find Parent Account No according to the Indentation number.
I have a table:
| No_ | Totaling | Indentation | Indentation -1 |
| 1 | 1..1Z | 0 | -1 |
| 11 | 11..11Z | 1 | 0 |
| 111 | 111..111Z | 2 | 1 |
| 1110 | 3 | 2 | |
| 1118 | 3 | 2 | |
| 1119 | 3 | 2 | |
| 112 | 112..112Z | 2 | 1 |
| 1120 | 3 | 2 | |
| 1128 | 3 | 2 | |
| 1129 | 3 | 2 | |
| 16 | 16..16Z | 1 | 0 |
| 160 | 160..160Z | 2 | 1 |
| 1600 | 1600..1600Z | 3 | 2 |
| 16000 | 4 | 3 | |
| 16009 | 4 | 3 |
The algorithm that I what to use is - to find and bring last account No_ where Indentitanion number is [Indentitation -1], if [Indentitation-1] = -1 than Blank.
The result table should look like this:
| No_ | Totaling | Indentation | Indentation -1 | Parent No_ |
| 1 | 1..1Z | 0 | -1 | |
| 11 | 11..11Z | 1 | 0 | 1 |
| 111 | 111..111Z | 2 | 1 | 11 |
| 1110 | 3 | 2 | 111 | |
| 1118 | 3 | 2 | 111 | |
| 1119 | 3 | 2 | 111 | |
| 112 | 112..112Z | 2 | 1 | 11 |
| 1120 | 3 | 2 | 112 | |
| 1128 | 3 | 2 | 112 | |
| 1129 | 3 | 2 | 112 | |
| 16 | 16..16Z | 1 | 0 | 1 |
| 160 | 160..160Z | 2 | 1 | 16 |
| 1600 | 1600..1600Z | 3 | 2 | 160 |
| 16000 | 4 | 3 | 160 | |
| 16009 | 4 | 3 | 160 |
I prefer to do this calculation in the Query Editor than DAX, but both solutions are welcome.
Anonymous
Try this method
First add an Index Column
Then this custom column
if [#"Indentation -1"]>-1 then Table.Max( Table.SelectRows(#"Added Index",(x)=>x[Index]<[Index] and x[Indentation]=[#"Indentation -1"]), "Index")[No_] else null
2 Replies
Anonymous
Try this method
First add an Index Column
Then this custom column
if [#"Indentation -1"]>-1 then Table.Max( Table.SelectRows(#"Added Index",(x)=>x[Index]<[Index] and x[Indentation]=[#"Indentation -1"]), "Index")[No_] else nullThanks, Zubair_Muhammad it works!