PowerQuery Column Generation

[unlistedgabriel]

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.

Client	Column3	CLIENT 1	Column5	CLIENT 2	Column7
		AUM (m)	Return	AUM (m)	Return
30/09/2016		180000000	-2.17645969
31/10/2016		174084888.4	-3.544963
30/11/2016		177363361.9	-3.380647
31/12/2016		183232446.4	3.315788
31/01/2017		180661810.7	-1.402937
28/02/2017		186725952.8	3.892258
31/03/2017		188936772.4	1.210827
30/04/2017		190923144.9	1.105857
31/05/2017		190013882.1	-0.151775
30/06/2017		184752376.8	-2.769011
31/07/2017		183107415.4	-0.078876
31/08/2017		190762813	4.180823
30/09/2017		184374321	-3.348919	202387702.8
31/10/2017		185767602.6	0.832585	214575115
30/11/2017		187891836.2	1.150004	215070822.5
31/12/2017		192509897.5	2.471796	215856533
31/01/2018		186591311.2	-2.650945
28/02/2018		186369733.6	0.055543
31/03/2018		190609944.9	2.126925	211871168
30/04/2018		185992468.3	-2.307564	207913904.3
31/05/2018		190650679.9	2.504516	213581279.3
30/06/2018		190126761.7	-0.243367	215061658.3	-0.48349
31/07/2018		189199326.2	0.126269	216840698.4	1.220376
31/08/2018		188959327	0.171114	218656100.9	0.624721
30/09/2018		186454866.6	-1.309723	217690288.7	-1.05
31/10/2018
30/11/2018
31/12/2018

<tbody>
</tbody>

 

[sandy666]

post example what you want to achieve
or
is that what you want?

Client	C1-AUM (m)	C1-Return	C2-AUM (m)	C2-Return
30/09/2016​	180​	-2.18%​
31/10/2016​	174.08​	-3.54%​
30/11/2016​	177.36​	-3.38%​
31/12/2016​	183.23​	3.32%​
31/01/2017​	180.66​	-1.40%​
28/02/2017​	186.73​	3.89%​
31/03/2017​	188.94​	1.21%​
30/04/2017​	190.92​	1.11%​
31/05/2017​	190.01​	-0.15%​
30/06/2017​	184.75​	-2.77%​
31/07/2017​	183.11​	-0.08%​
31/08/2017​	190.76​	4.18%​
30/09/2017​	184.37​	-3.35%​	202.39​
31/10/2017​	185.77​	0.83%​	214.58​
30/11/2017​	187.89​	1.15%​	215.07​
31/12/2017​	192.51​	2.47%​	215.86​
31/01/2018​	186.59​	-2.65%​
28/02/2018​	186.37​	0.06%​
31/03/2018​	190.61​	2.13%​	211.87​
30/04/2018​	185.99​	-2.31%​	207.91​
31/05/2018​	190.65​	2.50%​	213.58​
30/06/2018​	190.13​	-0.24%​	215.06​	-0.48%​
31/07/2018​	189.2​	0.13%​	216.84​	1.22%​
31/08/2018​	188.96​	0.17%​	218.66​	0.62%​
30/09/2018​	186.45​	-1.31%​	217.69​	-1.05%​
31/10/2018​
30/11/2018​
31/12/2018​

 

[unlistedgabriel]

Apologies! Yes that is essentially what I am looking for as an output (new to the forum if you couldn't guess!).

 

[sandy666]

ok, but...
proper table has one row of headers
proper table shouldn't have moxed data in columns (text vs numbers)

so maybe try this

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client", type date}, {"Column3", type any}, {"CLIENT 1", type any}, {"Column5", type any}, {"CLIENT 2", type any}, {"Column7", type any}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","AUM (m)","C1-AUM (m)",Replacer.ReplaceValue,{"CLIENT 1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Return","C1-Return",Replacer.ReplaceValue,{"Column5"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","AUM (m)","C2-AUM (m)",Replacer.ReplaceValue,{"CLIENT 2"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Return","C2-Return",Replacer.ReplaceValue,{"Column7"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Replaced Value3", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Blank Rows",{"Column3"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column1", "Client"}}),
    #"Divided Column" = Table.TransformColumns(#"Renamed Columns", {{"C1-AUM (m)", each _ / 1000000, type number}}),
    #"Divided Column1" = Table.TransformColumns(#"Divided Column", {{"C2-AUM (m)", each _ / 1000000, type number}}),
    #"Rounded Off" = Table.TransformColumns(#"Divided Column1",{{"C1-AUM (m)", each Number.Round(_, 2), type number}, {"C2-AUM (m)", each Number.Round(_, 2), type number}}),
    #"Divided Column2" = Table.TransformColumns(#"Rounded Off", {{"C1-Return", each _ / 100, type number}}),
    #"Divided Column3" = Table.TransformColumns(#"Divided Column2", {{"C2-Return", each _ / 100, type number}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Divided Column3",{{"C1-Return", Percentage.Type}, {"C2-Return", Percentage.Type}})
in
    #"Changed Type2"[/SIZE]

 

[unlistedgabriel]

Hi Thanks alot for this - am I right in thinking it would work no matter how many clients are added or removed from the source data? So If i delete out a client on the source data and refresh it will still produce useable columns in the output?

As you said its not a proper table with 2 lots of headers so I could concatenate those by creating a "CLIENT 1 & AUM (m)" and the same for the Returns? and then if it contains AUM it Transforms columns by the required calculations. This way it can be still recognised at a later step if I were to source these calculations to another query/sheet.

The query needs to be able to change any columns figures that has AUM/Returns, no matter the number of clients. I imagine using an IF there is a date in CLIENT column perform Transform.

I will have a play with your idea above and let you know!

Taking your example above:

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Client[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]CLIENT1-AUM (m)[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]CLIENT1-Return[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]CLIENT2-AUM (m)[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]CLIENT2-Return[/COLOR]
30/09/2016​	180​	-2.18%​
31/10/2016​	174.08​	-3.54%​
30/11/2016​	177.36​	-3.38%​
31/12/2016​	183.23​	3.32%​
31/01/2017​	180.66​	-1.40%​
28/02/2017​	186.73​	3.89%​
31/03/2017​	188.94​	1.21%​
30/04/2017​	190.92​	1.11%​
31/05/2017​	190.01​	-0.15%​
30/06/2017​	184.75​	-2.77%​
31/07/2017​	183.11​	-0.08%​
31/08/2017​	190.76​	4.18%​
30/09/2017​	184.37​	-3.35%​	202.39​
31/10/2017​	185.77​	0.83%​	214.58​
30/11/2017​	187.89​	1.15%​	215.07​
31/12/2017​	192.51​	2.47%​	215.86​
31/01/2018​	186.59​	-2.65%​
28/02/2018​	186.37​	0.06%​
31/03/2018​	190.61​	2.13%​	211.87​
30/04/2018​	185.99​	-2.31%​	207.91​
31/05/2018​	190.65​	2.50%​	213.58​
30/06/2018​	190.13​	-0.24%​	215.06​	-0.48%​
31/07/2018​	189.2​	0.13%​	216.84​	1.22%​
31/08/2018​	188.96​	0.17%​	218.66​	0.62%​
30/09/2018​	186.45​	-1.31%​	217.69​	-1.05%​
31/10/2018​
30/11/2018​
31/12/2018​

<tbody>
</tbody>

 

[sandy666]

Honestly I don't know what are you tryin' to achieve except dividing by 1000000 and by 100 ?

 

[sandy666]

my suggestion is that:

Source

Pivot

Date

Aum(m)

Aum(m) Value

Return

Return Value

Client1

Client2

Client3

30/09/2016​

Client1

180000000​

Return1

-2.17645969​

Return1

Return2

Return3

31/10/2016​

Client1

174084888.4​

Return1

-3.544963​

S.Aum(m) Value

S.Return Value

S.Aum(m) Value

S.Return Value

S.Aum(m) Value

S.Return Value

30/11/2016​

Client1

177363361.9​

Return1

-3.380647​

30/09/2016​

180​

-2.18%​

31/12/2016​

Client1

183232446.4​

Return1

3.315788​

31/10/2016​

174.08​

-3.54%​

31/01/2017​

Client1

180661810.7​

Return1

-1.402937​

30/11/2016​

177.36​

-3.38%​

28/02/2017​

Client1

186725952.8​

Return1

3.892258​

31/12/2016​

183.23​

3.32%​

31/03/2017​

Client1

188936772.4​

Return1

1.210827​

31/01/2017​

180.66​

-1.40%​

30/04/2017​

Client1

190923144.9​

Return1

1.105857​

28/02/2017​

186.73​

3.89%​

31/05/2017​

Client1

190013882.1​

Return1

-0.151775​

31/03/2017​

188.94​

1.21%​

30/06/2017​

Client1

184752376.8​

Return1

-2.769011​

30/04/2017​

190.92​

1.11%​

31/07/2017​

Client1

183107415.4​

Return1

-0.078876​

31/05/2017​

190.01​

-0.15%​

31/08/2017​

Client1

190762813​

Return1

4.180823​

30/06/2017​

184.75​

-2.77%​

30/09/2017​

Client1

184374321​

Return1

-3.348919​

31/07/2017​

183.11​

-0.08%​

31/10/2017​

Client1

185767602.6​

Return1

0.832585​

31/08/2017​

190.76​

4.18%​

30/11/2017​

Client1

187891836.2​

Return1

1.150004​

30/09/2017​

184.37​

-3.35%​

202.39​

202.39​

31/12/2017​

Client1

192509897.5​

Return1

2.471796​

31/10/2017​

185.77​

0.83%​

214.58​

214.58​

31/01/2018​

Client1

186591311.2​

Return1

-2.650945​

30/11/2017​

187.89​

1.15%​

215.07​

215.07​

28/02/2018​

Client1

186369733.6​

Return1

0.055543​

31/12/2017​

192.51​

2.47%​

215.86​

215.86​

31/03/2018​

Client1

190609944.9​

Return1

2.126925​

31/01/2018​

186.59​

-2.65%​

30/04/2018​

Client1

185992468.3​

Return1

-2.307564​

28/02/2018​

186.37​

0.06%​

31/05/2018​

Client1

190650679.9​

Return1

2.504516​

31/03/2018​

190.61​

2.13%​

211.87​

211.87​

30/06/2018​

Client1

190126761.7​

Return1

-0.243367​

30/04/2018​

185.99​

-2.31%​

207.91​

207.91​

31/07/2018​

Client1

189199326.2​

Return1

0.126269​

31/05/2018​

190.65​

2.50%​

213.58​

213.58​

31/08/2018​

Client1

188959327​

Return1

0.171114​

30/06/2018​

190.13​

-0.24%​

215.06​

-0.48%​

215.06​

-0.48%​

30/09/2018​

Client1

186454866.6​

Return1

-1.309723​

31/07/2018​

189.2​

0.13%​

216.84​

1.22%​

216.84​

1.22%​

30/09/2016​

Client2

Return2

31/08/2018​

188.96​

0.17%​

218.66​

0.62%​

218.66​

0.62%​

31/10/2016​

Client2

Return2

30/09/2018​

186.45​

-1.31%​

217.69​

-1.05%​

217.69​

-1.05%​

30/11/2016​

Client2

Return2

31/10/2018​

31/12/2016​

Client2

Return2

30/11/2018​

31/01/2017​

Client2

Return2

31/12/2018​

28/02/2017​

Client2

Return2

31/03/2017​

Client2

Return2

30/04/2017​

Client2

Return2

31/05/2017​

Client2

Return2

30/06/2017​

Client2

Return2

31/07/2017​

Client2

Return2

31/08/2017​

Client2

Return2

30/09/2017​

Client2

202387702.8​

Return2

31/10/2017​

Client2

214575115​

Return2

30/11/2017​

Client2

215070822.5​

Return2

31/12/2017​

Client2

215856533​

Return2

31/01/2018​

Client2

Return2

28/02/2018​

Client2

Return2

31/03/2018​

Client2

211871168​

Return2

30/04/2018​

Client2

207913904.3​

Return2

31/05/2018​

Client2

213581279.3​

Return2

30/06/2018​

Client2

215061658.3​

Return2

-0.48349​

31/07/2018​

Client2

216840698.4​

Return2

1.220376​

31/08/2018​

Client2

218656100.9​

Return2

0.624721​

30/09/2018​

Client2

217690288.7​

Return2

-1.05​

31/10/2018​

Client2

Return2

30/11/2018​

Client2

Return2

31/12/2018​

Client2

Return2

30/09/2016​

Client3

Return3

31/10/2016​

Client3

Return3

30/11/2016​

Client3

Return3

31/12/2016​

Client3

Return3

31/01/2017​

Client3

Return3

28/02/2017​

Client3

Return3

31/03/2017​

Client3

Return3

30/04/2017​

Client3

Return3

31/05/2017​

Client3

Return3

30/06/2017​

Client3

Return3

31/07/2017​

Client3

Return3

31/08/2017​

Client3

Return3

30/09/2017​

Client3

202387702.8​

Return3

31/10/2017​

Client3

214575115​

Return3

30/11/2017​

Client3

215070822.5​

Return3

31/12/2017​

Client3

215856533​

Return3

31/01/2018​

Client3

Return3

28/02/2018​

Client3

Return3

31/03/2018​

Client3

211871168​

Return3

30/04/2018​

Client3

207913904.3​

Return3

31/05/2018​

Client3

213581279.3​

Return3

30/06/2018​

Client3

215061658.3​

Return3

-0.48349​

31/07/2018​

Client3

216840698.4​

Return3

1.220376​

31/08/2018​

Client3

218656100.9​

Return3

0.624721​

30/09/2018​

Client3

217690288.7​

Return3

-1.05​

31/10/2018​

Client3

Return3

30/11/2018​

Client3

Return3

31/12/2018​

Client3

Return3

load source table into PowerQuery , divide columns by 1000000 and by 100 suitable then create PivotTable from QueryTable and set appropriate Values to format what you want
now you can add as many clients as you like

 

[unlistedgabriel]

Thanks - Really appreciate the suggestions. Apologies if I wasn't totally clear.

 

[sandy666]

You're welcome
Have a nice day