1 data set, 20 odd fixed columns, 9 dynamic columns

[Anonymous]

Hi,

** Apologies in advance for the bulk of text - not sure how to summarise this in to a short question!

I am working with healthcare data, trying to build a data quality dashboard that shows some metrics as to data qualty, but also just the raw data itself. Schemes have been created to break up the data to allow service users to fix the data quality issues present.

I have 30 odd schemes and they all require the 20 odd 'fixed'/'defined' columns to be shown, the only issue is that each scheme then potentially has up to 4 extra columns that are included to assist the end user validate the data quality issue. 

The origins of this was an excel file and that worked as each scheme sat on a seperate excel tab, and any of the (up to) 4 extra columns could be named in their respective tabs. To make this work in Power BI I thought the best way would be to have one dataset ( now in SQL Server - brought in to Power BI via a SQL view) and then have 3 Varchar columns, 3 Date columns, and 3 INT columns (as the 9 'dynamic columns'). The only issue is that a varchar in one scheme might mean something, and something completely different in another scheme, or might be NULL because that scheme doesnt have a need to bring anything Varchar related in to it.

At the moment I've replicated the 30 individual report tabs in to Power BI so that I can name those 9 dynamic columns that I do bring in, I've then made bookmarks to those tabs and was going to create buttons with actions to jump in to - but I really dont feel this is the best way to do it. It also means I cant use drillthrough off a visual to one report tab.

The two issues here I am trying to resolve are:

• How to otherwise name the 3 Varchar columns, 3 Date columns, and 3 INT columns dynamically depending on what the user has chosen to drill-down on
• Whether any columns that appear as NULL all the way through can be dropped or hidden depending on the drill-down choice the user has made. Given the large number of columns I'm trying to show as few as possible to the end-user

Is this at all feasible in Power BI, or is it something I'd need to go back in to SQL to fix. Can't work out how to go about fixing this - any help appreciated

Many thanks!

[Anonymous]

Sample table (inserted as picture as it wouldnt let me post as a table here):

Limited by the 10 columns limit here, but effectively columns 1-4 (scheme to specialty) I have 20 odd (that dont change from scheme to scheme), and then I have 9 (3 varchar, 3 dates, 3 ints) that do change scheme to scheme.

So for scheme 1 date_1 refers to the previous attended appointment, date_2 refers to the next/future appointment, and int_1 refers to the weeks between the two appointments. They would be ideally given short hand names such as that. The varchar_1, varchar_2 and int_2 columnsare null for this scheme. This scheme might have say 1000 rows.

Scheme 2 and 5 (say 500 rows each) do not have any data at all in the varchar, date and int columns. Scheme 3 varchar_1 refers to an admission ward code/name, and the date_1 column is the future admission date. Scheme 4 varchar_1 refers to the priority of the appointment, varchar_2 to the fact that its the patients first appointment and date_1 to the referral date from their medical practioner.

Each scheme will have the same varchar, date and int columns completed. Ideally i want to create a summary page that shows counts by scheme and then allow the user to drill down in to a specific scheme (say scheme 1) and see a dataset with the varchar, date and int columns named appropriately as to that scheme, and if possible, the other NULL columns dropped out.

I did have this before as individual named columns but that was creating around 15 columns (with potential for it to increase further if more schemes are added) and I want to try and present as small a table of data as possible to the end user

At this moment in time I am using a direct query to a SQL view so not sure unpivoting works with that but will look in to it a bit more.

Thanks again!

[Anonymous]

@amitchandak @Greg_Deckler is the sample data above sufficient? I can expand further if required?

Thanks again

[amitchandak]

@Anonymous ,

As already suggested by @Greg_Deckler , You need to provide some better data. In between please refer too.

https://radacad.com/pivot-and-unpivot-with-power-bi

https://docs.microsoft.com/en-us/power-bi/guidance/

[Greg_Deckler]

In general agreement with @MattAllington. Sample data would really help in sorting this all out though. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[MattAllington]

You should consider unpivoting the data from columns into rows using power query during load to power BI. That is a much better data structure