Power Query / generation of table from a power BI desktop table

[miguesnet]

Hi

Is is possible to generate automatically in table in power query from a table created in power BI desktop with different calculated measures?

Thanks in advance 

Mickael

[dax]

Hi miguesnet,

I am not clear about your requirement, did you want to get measure in Power Query? The measure won't show in power query, they are different, one is M code(which will show the transformation of table) , one is DAX (create aggregration of table).

If this is not what you want, please corrrect me and explain your requirement in details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[miguesnet]

Hi Zoe, 

I will try to detail what i would like to do.

I want to ultimately create a waterfall chart.

My datas are at a daily level.

I want to aggregatte data at a monthly level taking all the lines for the amount ( criteria 1 and 0) and only lines with criteria 1 for KG

I need at this level to add 3 calculated columns : Effect 1 , Effect 2 et Effect 3 ( based on N and N-1 values)

Step 1 : Agreggate data

I want then to be able to depivot column to create automatically  a third table  that i will use to do waterfall charts.

Waterfall chart

Hope this is clear.

Thanks in advance for your help.

Best Regards

Mickael

[dax]

Hi miguesnet,

I couldn't understand your logic of calculated columns(effect1, effect2, effect3 which based on N and N-1 values), so if possible could you please explain to  me in details?

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[miguesnet]

Hi 

N = This year

N-1 = Last year.

Calculated colums will be based on the following formulas but this is not what was blocking me. 

Effect 1 =  (Cost.kg N - Cost .kg N-1)* Kg N

Effect 2 =  (Kg N - KG N-1)* (Cost.kg N - Avge Cost . Kg N-1)

Effect 3 =   (Kg N - KG N-1)* Avge Cost . Kg N-1

Cost.kg = Amount / kg

I was struggling to go through the differents steps 

1. Agregation of datas

2. Calculation of effect

3. Depivoting of columns

4. Waterfall charts

Hope this makes sense

Regards

Mickael

[dax]

Hi miguesnet,

I want to know whether Cost.kg = Amount / kg need to exclude criteria=0's record? You could refer to my sample to see whether it work or not. If this is not what you want, please inform me more deatiled information(such as your data sample and your expected output).

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[miguesnet]

Hi,

Thanks for your answer. That is where i managed to go so far. Utimately, my goal is to present datas uner a waterfall chart . I wanted to upload a pbix file but unfortunately i am not able.

In order to demonstrate what i want to achieve :

- From the second table with effect calculation that you have done  : i extracted it into Excel and uploaded it in power query ( not really efficient ! 

- I then created a second table which makes reference to the first one and then I depivoted column effect 1 , 2 and 3

- which then allowed me to do the waterfall chart with the effect analysis

I would like to carry out all those steps automatically.

Thanks 

Mickael