Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I hope you can help.
I have a report that summarises POs for each account, and then before the next account it shows the account number and overall value.
Ideally I need to show the related account number next to each of the POs, as this joins to another table which needs summarising - the data looks like the image on the left
PO Number | Value | Account | Overall | PO Number | Value | New Account Column | Overall | |
O000100/01252 | 9.2 | O000100/01252 | 9.2 | 100.2011 | ||||
0 | 100.2011 | 9.2 | 0 | 100.2011 | 9.2 | |||
O000100/01228 | 91.71 | O000100/01228 | 91.71 | 100.2104 | ||||
0 | 100.2104 | 91.71 | 0 | 100.2104 | 91.71 | |||
O000950/02810 | 16.7 | O000950/02810 | 16.7 | 100.2211 | ||||
0 | 100.2211 | 16.7 | 0 | 100.2211 | 16.7 | |||
O000100/01303 | 469 | O000100/01303 | 469 | 100.2242 | ||||
0 | 100.2242 | 469 | 0 | 100.2242 | 469 | |||
O000100/01304 | 70.38 | O000100/01304 | 70.38 | 100.2334 | ||||
0 | 100.2334 | 70.38 | 0 | 100.2334 | 70.38 | |||
O000100/01227 | 95 | O000100/01227 | 95 | 100.241 | ||||
O000100/01227 | 95 | O000100/01227 | 95 | 100.241 | ||||
O000100/01227 | 95 | O000100/01227 | 95 | 100.241 | ||||
O000100/01298 | 290 | O000100/01298 | 290 | 100.241 | ||||
0 | 100.241 | 575 | 0 | 100.241 | 575 |
I have tried creating a dervived table, considered lookups (which went on forever), and hoped there would be a next non blank value, but nothing seems to work.
I have indexed the table to see if that would help, but have reached an impasse where the brain just won't function anymore.
If anyone could suggest a formula which essentially would be:
If Account<>Blank() Then Account, else equal next non blank in Account Column I would really appreciate it.
Many thanks
Solved! Go to Solution.
Hi @ChrisCam ,
You can achieve this in powerquery with couple of steps, See below:
1st 4 columns is originated data last columns is desired out put data
To achieve this Go to Edit Queries -->> Transform tab --->> Click on Fill (See below img)
Thanks & Regards,
B V S S
Apologies the table I attached was not very clear - I need to add a new column which should reflect the below:
PO Number | Value | Account | New Account Column | Overall | ||
O000100/01252 | 9.2 | 100.2011 | ||||
0 | 100.2011 | 100.2011 | 9.2 | |||
O000100/01228 | 91.71 | 100.2104 | ||||
0 | 100.2104 | 100.2104 | 91.71 | |||
O000950/02810 | 16.7 | 100.2211 | ||||
0 | 100.2211 | 100.2211 | 16.7 | |||
O000100/01303 | 469 | 100.2242 | ||||
0 | 100.2242 | 100.2242 | 469 | |||
O000100/01304 | 70.38 | 100.2334 | ||||
0 | 100.2334 | 100.2334 | 70.38 | |||
O000100/01227 | 95 | 100.241 | ||||
O000100/01227 | 95 | 100.241 | ||||
O000100/01227 | 95 | 100.241 | ||||
O000100/01298 | 290 | 100.241 | ||||
0 | 100.241 | 100.241 | 575 |
Hi @ChrisCam ,
1st 3 Columns are your Actual data and Last 3 Columns are your desired output. Is it correct??
Hi,
The originating data is [PO Number]; [Value]; [Account] and [Overall]
The desired output is the column called "New Account Column"
Thanks
Hi @ChrisCam ,
You can achieve this in powerquery with couple of steps, See below:
1st 4 columns is originated data last columns is desired out put data
To achieve this Go to Edit Queries -->> Transform tab --->> Click on Fill (See below img)
Thanks & Regards,
B V S S
This is fantastic - thank you ever so much for such a quick response, and making me aware of a feature I did not even know existed
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |