cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
miguesnet Frequent Visitor
Frequent Visitor

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

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

6 REPLIES 6
Community Support Team
Community Support Team

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

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 Frequent Visitor
Frequent Visitor

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

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.

 

photo 5.png

 

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)

 

photo 6.pngStep 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.

 

photo 8png.pngWaterfall chart

Hope this is clear.

Thanks in advance for your help.

 

Best Regards

 

Mickael

 

Community Support Team
Community Support Team

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

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 Frequent Visitor
Frequent Visitor

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

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

Community Support Team
Community Support Team

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

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 Frequent Visitor
Frequent Visitor

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

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 ! Smiley Happy

Annotation 2019-10-17 172346.png

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

Annotation 2019-10-17 172608.png

 

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

 

Annotation 2019-10-17 172609.png

I would like to carry out all those steps automatically.

 

Thanks 

 

Mickael

 

 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (3,630)