unlistedgabriel
New Member
- Joined
- Feb 4, 2019
- Messages
- 4
Good Afternoon
I am hoping someone might know the answer to my conundrum - I have a source of data which is a list of client returns/values, this list is provided by an external source and is in the same format every time, however the number of clients might increase or decrease, and there are also a large number of them so manually amending would not be sufficient.
I am looking for a way for PowerQuery to amend a columns values ("AUM (m)" dividing the value by 1000000, "Return" to be converted to a percentage) based on if the headers "AUM (m)" and "Returns" are present in the source file. Each Client has both an AUM (m) and a Returns column.
From my initial investigations I am trying to decide on something that just generates a custom column and does the calculation but then replaces the AUM columns per Client. Or if it were easier with a custom function being called?
For the Returns column I want to apply the percentage type, but again IF the workbook as "Return" in Row {0}. This may also need a divisable by 100 applying first but based on the other columns needs it should be a relatively simple job duplicating that step.
If anyone has a good idea for the best practice of this I am all ears. Below is an example of the kind of thing I'm dealing with, thank you in advance for any assistance given!
Top row in the below table is the HEADERS and then the first row (Row{0}) is below that.
[TABLE="width: 446"]
<tbody>[TR]
[TD]Client[/TD]
[TD]Column3[/TD]
[TD]CLIENT 1[/TD]
[TD]Column5[/TD]
[TD]CLIENT 2[/TD]
[TD]Column7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]AUM (m)[/TD]
[TD]Return[/TD]
[TD]AUM (m)[/TD]
[TD]Return[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/09/2016[/TD]
[TD][/TD]
[TD="align: right"]180000000[/TD]
[TD="align: right"]-2.17645969[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/10/2016[/TD]
[TD][/TD]
[TD="align: right"]174084888.4[/TD]
[TD="align: right"]-3.544963[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/11/2016[/TD]
[TD][/TD]
[TD="align: right"]177363361.9[/TD]
[TD="align: right"]-3.380647[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/12/2016[/TD]
[TD][/TD]
[TD="align: right"]183232446.4[/TD]
[TD="align: right"]3.315788[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/01/2017[/TD]
[TD][/TD]
[TD="align: right"]180661810.7[/TD]
[TD="align: right"]-1.402937[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28/02/2017[/TD]
[TD][/TD]
[TD="align: right"]186725952.8[/TD]
[TD="align: right"]3.892258[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/03/2017[/TD]
[TD][/TD]
[TD="align: right"]188936772.4[/TD]
[TD="align: right"]1.210827[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/04/2017[/TD]
[TD][/TD]
[TD="align: right"]190923144.9[/TD]
[TD="align: right"]1.105857[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/05/2017[/TD]
[TD][/TD]
[TD="align: right"]190013882.1[/TD]
[TD="align: right"]-0.151775[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/06/2017[/TD]
[TD][/TD]
[TD="align: right"]184752376.8[/TD]
[TD="align: right"]-2.769011[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/07/2017[/TD]
[TD][/TD]
[TD="align: right"]183107415.4[/TD]
[TD="align: right"]-0.078876[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/08/2017[/TD]
[TD][/TD]
[TD="align: right"]190762813[/TD]
[TD="align: right"]4.180823[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/09/2017[/TD]
[TD][/TD]
[TD="align: right"]184374321[/TD]
[TD="align: right"]-3.348919[/TD]
[TD="align: right"]202387702.8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/10/2017[/TD]
[TD][/TD]
[TD="align: right"]185767602.6[/TD]
[TD="align: right"]0.832585[/TD]
[TD="align: right"]214575115[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/11/2017[/TD]
[TD][/TD]
[TD="align: right"]187891836.2[/TD]
[TD="align: right"]1.150004[/TD]
[TD="align: right"]215070822.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/12/2017[/TD]
[TD][/TD]
[TD="align: right"]192509897.5[/TD]
[TD="align: right"]2.471796[/TD]
[TD="align: right"]215856533[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/01/2018[/TD]
[TD][/TD]
[TD="align: right"]186591311.2[/TD]
[TD="align: right"]-2.650945[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28/02/2018[/TD]
[TD][/TD]
[TD="align: right"]186369733.6[/TD]
[TD="align: right"]0.055543[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/03/2018[/TD]
[TD][/TD]
[TD="align: right"]190609944.9[/TD]
[TD="align: right"]2.126925[/TD]
[TD="align: right"]211871168[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/04/2018[/TD]
[TD][/TD]
[TD="align: right"]185992468.3[/TD]
[TD="align: right"]-2.307564[/TD]
[TD="align: right"]207913904.3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/05/2018[/TD]
[TD][/TD]
[TD="align: right"]190650679.9[/TD]
[TD="align: right"]2.504516[/TD]
[TD="align: right"]213581279.3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/06/2018[/TD]
[TD][/TD]
[TD="align: right"]190126761.7[/TD]
[TD="align: right"]-0.243367[/TD]
[TD="align: right"]215061658.3[/TD]
[TD="align: right"]-0.48349[/TD]
[/TR]
[TR]
[TD="align: right"]31/07/2018[/TD]
[TD][/TD]
[TD="align: right"]189199326.2[/TD]
[TD="align: right"]0.126269[/TD]
[TD="align: right"]216840698.4[/TD]
[TD="align: right"]1.220376[/TD]
[/TR]
[TR]
[TD="align: right"]31/08/2018[/TD]
[TD][/TD]
[TD="align: right"]188959327[/TD]
[TD="align: right"]0.171114[/TD]
[TD="align: right"]218656100.9[/TD]
[TD="align: right"]0.624721[/TD]
[/TR]
[TR]
[TD="align: right"]30/09/2018[/TD]
[TD][/TD]
[TD="align: right"]186454866.6[/TD]
[TD="align: right"]-1.309723[/TD]
[TD="align: right"]217690288.7[/TD]
[TD="align: right"]-1.05[/TD]
[/TR]
[TR]
[TD="align: right"]31/10/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/11/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/12/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I am hoping someone might know the answer to my conundrum - I have a source of data which is a list of client returns/values, this list is provided by an external source and is in the same format every time, however the number of clients might increase or decrease, and there are also a large number of them so manually amending would not be sufficient.
I am looking for a way for PowerQuery to amend a columns values ("AUM (m)" dividing the value by 1000000, "Return" to be converted to a percentage) based on if the headers "AUM (m)" and "Returns" are present in the source file. Each Client has both an AUM (m) and a Returns column.
From my initial investigations I am trying to decide on something that just generates a custom column and does the calculation but then replaces the AUM columns per Client. Or if it were easier with a custom function being called?
For the Returns column I want to apply the percentage type, but again IF the workbook as "Return" in Row {0}. This may also need a divisable by 100 applying first but based on the other columns needs it should be a relatively simple job duplicating that step.
If anyone has a good idea for the best practice of this I am all ears. Below is an example of the kind of thing I'm dealing with, thank you in advance for any assistance given!
Top row in the below table is the HEADERS and then the first row (Row{0}) is below that.
[TABLE="width: 446"]
<tbody>[TR]
[TD]Client[/TD]
[TD]Column3[/TD]
[TD]CLIENT 1[/TD]
[TD]Column5[/TD]
[TD]CLIENT 2[/TD]
[TD]Column7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]AUM (m)[/TD]
[TD]Return[/TD]
[TD]AUM (m)[/TD]
[TD]Return[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/09/2016[/TD]
[TD][/TD]
[TD="align: right"]180000000[/TD]
[TD="align: right"]-2.17645969[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/10/2016[/TD]
[TD][/TD]
[TD="align: right"]174084888.4[/TD]
[TD="align: right"]-3.544963[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/11/2016[/TD]
[TD][/TD]
[TD="align: right"]177363361.9[/TD]
[TD="align: right"]-3.380647[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/12/2016[/TD]
[TD][/TD]
[TD="align: right"]183232446.4[/TD]
[TD="align: right"]3.315788[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/01/2017[/TD]
[TD][/TD]
[TD="align: right"]180661810.7[/TD]
[TD="align: right"]-1.402937[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28/02/2017[/TD]
[TD][/TD]
[TD="align: right"]186725952.8[/TD]
[TD="align: right"]3.892258[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/03/2017[/TD]
[TD][/TD]
[TD="align: right"]188936772.4[/TD]
[TD="align: right"]1.210827[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/04/2017[/TD]
[TD][/TD]
[TD="align: right"]190923144.9[/TD]
[TD="align: right"]1.105857[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/05/2017[/TD]
[TD][/TD]
[TD="align: right"]190013882.1[/TD]
[TD="align: right"]-0.151775[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/06/2017[/TD]
[TD][/TD]
[TD="align: right"]184752376.8[/TD]
[TD="align: right"]-2.769011[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/07/2017[/TD]
[TD][/TD]
[TD="align: right"]183107415.4[/TD]
[TD="align: right"]-0.078876[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/08/2017[/TD]
[TD][/TD]
[TD="align: right"]190762813[/TD]
[TD="align: right"]4.180823[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/09/2017[/TD]
[TD][/TD]
[TD="align: right"]184374321[/TD]
[TD="align: right"]-3.348919[/TD]
[TD="align: right"]202387702.8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/10/2017[/TD]
[TD][/TD]
[TD="align: right"]185767602.6[/TD]
[TD="align: right"]0.832585[/TD]
[TD="align: right"]214575115[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/11/2017[/TD]
[TD][/TD]
[TD="align: right"]187891836.2[/TD]
[TD="align: right"]1.150004[/TD]
[TD="align: right"]215070822.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/12/2017[/TD]
[TD][/TD]
[TD="align: right"]192509897.5[/TD]
[TD="align: right"]2.471796[/TD]
[TD="align: right"]215856533[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/01/2018[/TD]
[TD][/TD]
[TD="align: right"]186591311.2[/TD]
[TD="align: right"]-2.650945[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28/02/2018[/TD]
[TD][/TD]
[TD="align: right"]186369733.6[/TD]
[TD="align: right"]0.055543[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/03/2018[/TD]
[TD][/TD]
[TD="align: right"]190609944.9[/TD]
[TD="align: right"]2.126925[/TD]
[TD="align: right"]211871168[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/04/2018[/TD]
[TD][/TD]
[TD="align: right"]185992468.3[/TD]
[TD="align: right"]-2.307564[/TD]
[TD="align: right"]207913904.3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/05/2018[/TD]
[TD][/TD]
[TD="align: right"]190650679.9[/TD]
[TD="align: right"]2.504516[/TD]
[TD="align: right"]213581279.3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/06/2018[/TD]
[TD][/TD]
[TD="align: right"]190126761.7[/TD]
[TD="align: right"]-0.243367[/TD]
[TD="align: right"]215061658.3[/TD]
[TD="align: right"]-0.48349[/TD]
[/TR]
[TR]
[TD="align: right"]31/07/2018[/TD]
[TD][/TD]
[TD="align: right"]189199326.2[/TD]
[TD="align: right"]0.126269[/TD]
[TD="align: right"]216840698.4[/TD]
[TD="align: right"]1.220376[/TD]
[/TR]
[TR]
[TD="align: right"]31/08/2018[/TD]
[TD][/TD]
[TD="align: right"]188959327[/TD]
[TD="align: right"]0.171114[/TD]
[TD="align: right"]218656100.9[/TD]
[TD="align: right"]0.624721[/TD]
[/TR]
[TR]
[TD="align: right"]30/09/2018[/TD]
[TD][/TD]
[TD="align: right"]186454866.6[/TD]
[TD="align: right"]-1.309723[/TD]
[TD="align: right"]217690288.7[/TD]
[TD="align: right"]-1.05[/TD]
[/TR]
[TR]
[TD="align: right"]31/10/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/11/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/12/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]