create a table from a power bi dataset

Question

Hi,I'm still a novice and have an issue which is difficualt to explain&nbsp;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 reason1 = number 1 reason2 = number 2 reason3 = number 3 reason.&nbsp;Looking at these 14 columns I have consolidated the reasons why people are leaving by creating 3 calculated columnsNo. 1 Ranked Reason - this column finds the number 1 ranked reason in the 14 columnsNo. 2 Ranked Reason - this column finds the number 2 ranked reason in the 14 columnsNo. 3 Ranked Reason - this column finds the number 3 ranked reason in the 14 columns&nbsp;From here I want to create a simple table within Power BI desktopColumn 1 heading would be "Reasons for leaving" and would be a consoldiated list of reasons (so only 14 rows). no duplicatesColumn 2 heading would be "ranked 1" and would count how many times the reason was ranked 1Columns 3 &amp; 4 would be the same as above for 2nd and 3rd rankings&nbsp;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&nbsp;

seward12533 · Answer

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 · Answer

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 · Answer

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 · Answer

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 · Answer

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.

Forum Discussion

create a table from a power bi dataset

7 Replies

Related content

Create new table

Lakehouse Table Generate Create Table

Create rows from 2 tables

Creating tables from a dataset

Creating Date Tables