Data Modeling for Power BI using Raw Data

[dasingh9]

Hi,
In the current scenaio, I have two tables (WORKSHOPS_TABLE, APPOINTMENTS_TABLE) in the data warehouse (see attached screenshot). In each table, Faculty column may contain multiple values like "Arts,Law" for each StudentID and I have created db views against each table to split Faculty values into multiple records and performed UNION ALL to generate a result set. Finally, I created a Faculty wise report (a column chart) based on the result set.

In the new scenaio, I have access to only two tables (WORKSHOPS_TABLE, APPOINTMENTS_TABLE) and don't have access to create views on the DWH. I have to generate the same report in Power BI. I am just exploring how to prepare my model in Power BI so that I can generate the report in Power BI in an flexible and efficient way. Could you please suggest some approach to prepare the data model for the new scenario. [Refer Screenshots]

Cheers,

Davinder

Faculty Wise Report - Old SystemFaculty Wise Report - New System

[Ashish_Mathur · Accepted solution]

Hi,

In the Query Editor window of PBI desktop, right click on the second column of the Workshops_Table and go to Split > Split b delimiters > Specify , as a seperator.  Under Advanced, select rows.

Hope this helps.

[v-piga-msft · Accepted solution]

Hi @dasingh9，

You could prepare your data model in Query Editor. Assuminng that you have two tables (WORKSHOPS_TABLE, APPOINTMENTS_TABLE).

1. Go to Query Editor

2. select one table then select the column Faculty to Split column by Delimiter

3. select columns Faculty.1 and Faculty.2 with Ctrl and click Unpivoted Columns

4. Rename the Value to Faculty

5. Remove the column Attribute

6. do the same option for another table

7. Close and Apply

Then you could create the chart below.

For reference, you could have a look at my test pbix file attached below.

Best Regards，

Cherry

[Ashish_Mathur · Accepted solution]

Hi,

In the Query Editor window of PBI desktop, right click on the second column of the Workshops_Table and go to Split > Split b delimiters > Specify , as a seperator.  Under Advanced, select rows.

Hope this helps.

[dasingh9]

Thanks @Ashish_Mathur and Cherry, both the solutions worked well.

[Ashish_Mathur]

You are welcome.