How to prepare table in power query to load to pbi

[Maieev]

Hi,

I tried to prepare this table to load to PBI but I cannot get the result I want.

I would like to get something like this, with timeline (days), delays (days) and lost sales (currency) as averages. I wanted to transpose table and match phases with names of the columns in pq but the result is still bad. Each Phase has some has several corresponding columns. So for example if in first slicer I pick Phase3/4 I would like to see on all graphs Phases 3, 4 and Phase3/4. Moreover, I would like to count how many times (also as a percentage, this first circular chart) there was a delay due to phase 3 and how many times from phase 4 or no delay (column O in excel file). It would be perfect if I could also drop this in Delays column chart to see which phase caused the delay.

Phase1	Phase1 timeline
	Phase1 delays
	Phase1 sales lost
Phase2	Phase2 timeline
Phase3	Phase3 timeline
	Phase3 delays
Phase4	Phase4 timeline
	Phase4 delays
Phase3/4	Phase3/4 delays
	Phase3/4 sales lost
	Phase3 timeline
	Phase3 delays
	Phase4 timeline
	Phase4 delays
Phase5	Phase5 timeline
	Phase5 delays
	Phase5 sales lost
Phase6	Phase6 timeline
	Phase6 delays
	Phase6 sales lost

If anyone could lead me to any solution, I'd be grateful. I am enclosing sample data. Thank you.

sample file: https://files.fm/u/c7xtu254c 

[Maieev · Accepted solution]

I managed to get some solution for this but in kinda different way. First I unpivoted columns as suggested but then I splited Attribute one by delimiter. That's how I got three columns:

so Stage means Phase1, Phase2, etc., Type is delays, timeline or sales lost and value is value. So from this point it is very simple to show the data because I can just put Type as a filter and show the data that I want :). Thanks for the suggestion.

[v-stephen-msft]

Hi @Maieev ,

You would try to unpivot the colums with Phase.

Below are the detailed steps.

1.Go to Power Query Editor, select all the columns with Phase, then click 'Unpivot Columns'.

Then you'll get a attribute column with different kinds of phase and the a value column with values.

2.Add a column from examples. Rename the column from examples as Phase.

Here's a simple result to display.

Best Regards,

Stephen Tao

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