create a table from a power bi dataset

[Anonymous]

Hi,

I'm still a novice and have an issue which is difficualt to explain

I have a data set for my work that gives me reasons why people are leaving the business I work for. Te way to data is presented is there are 14 reasons why people leave. Each reason is is a data column with the heading being the reason. The data in the columns are either blank, 1, 2 ro 3.

blank = was not a reason

1 = number 1 reason

2 = number 2 reason

3 = number 3 reason.

Looking at these 14 columns I have consolidated the reasons why people are leaving by creating 3 calculated columns

No. 1 Ranked Reason - this column finds the number 1 ranked reason in the 14 columns

No. 2 Ranked Reason - this column finds the number 2 ranked reason in the 14 columns

No. 3 Ranked Reason - this column finds the number 3 ranked reason in the 14 columns

From here I want to create a simple table within Power BI desktop

Column 1 heading would be "Reasons for leaving" and would be a consoldiated list of reasons (so only 14 rows). no duplicates

Column 2 heading would be "ranked 1" and would count how many times the reason was ranked 1

Columns 3 & 4 would be the same as above for 2nd and 3rd rankings

Is there any way to create this table please as I have trying researching. By doing thise I can have consolidated visuals my staeholders.

Thank you

[v-lili6-msft]

Hi@jbartlett

 You can duplicate the table and remove other 26 columns and then use unpivot  function  to UNPIVOT the data in Edit Queries like this:

now it don't effect basic table.

Best Regards,

Lin

[Anonymous]

Yeah thats the path I am going down but wanted to know if I could avoid it....

[Seward12533]

The best way is to UNPIVOT the data so its more of a database format.  In PowerQuery (Edit Query) either select all 14 reason columns or the other columns and choose UNPIVOT from the transform menu. 

Then just write a simple measure and build some visuals you can use the TOP N to just pick the top 3

Number of Occurances = SUM(AbsenseReasons[Num Occ])

Here is a link to a demo file 

https://1drv.ms/u/s!AuCIkLeqFmlhhJhyveXuQpnJYPjTSg

[Anonymous]

I can see what your thinking.... it would require pulling the data twice. though,... the are over 40 columns of data in the table (I only refered to the 14 in my prvious message). So i wouldn't want to unpivot it all. I'd have to do a second data pull of only those 14 columns and then unpivot.... kinda wanting to avoid that

[Seward12533]

Why not? PowerBI is incredibly efficient at tall narrow tables with limited number of distinct values. I saw a demo at a recent power bi summit where expert showed significant performance improvement by doing something similar. Trying to deal with columnar delimited data like this goes against the grain of how PowerBI works and will make your life and Dax very complicated and your workbook slow.

[Anonymous]

I dont want to unpivot the whole data table. thre are many columns. The first column being a timedate stamp. and the data is growing so not practical to unpivot the whole daya table

[Seward12533]

Up to you it’s the right way. Whole counter intuitive it’s the best way handle it. Otherwise you will probably need to write a measure to calculate each of the columns individually. Could be impractical if your adding new reasons.