ETL on Power BI

[kenobi900]

I am new to power BI, i am to did the following task

 3 CSV files.

1. Census Data – This dataset includes all census questions and responses.
2. Geodata – This dataset includes all postcodes within the Census with the relevant city and region.
3. Questions – This dataset includes a list of all questions that can be within the Census. The “Question_Order” column on this dataset is a question ID. This question ID relates to the first 3 characters of each question column header in the Census Data file.

you should demonstrate the use of both joining datasets together and creating relationships between datasets in Power BI.  You should also demonstrate the use of at least one calculated column and one measure

link to pbix : https://drive.google.com/file/d/12ijYxI8luyxAqiywmoMSFLK1RsKCAymP/view?usp=sharing , kindly help me confirm if i did the right thing.

the first task is 

1.  Create a list of all unique questions and provided answers from the Census data.

This should be displayed in a tabular format, with the question in Column 1 and all provided answers as a list, separated by a semi-colon, in column 2.

[kenobi900]

[v-jingzhan-msft · Accepted solution]

Hi @kenobi900 

You can do the following steps in Power Query:

In Census Data query, select all the columns except all answer columns, click unpivot other columns. This will unpivot all answer columns. 

You will then have two columns: "Attribute" column has questions and "Value" has answers. Split "Attribute" column by Digit to Non-Digit. Then rename columns. 

In Questions query, merge Census Data table to it on Question Order column.

Then expand the merged result column and select only Answer column for expanding. 

Group by QuestionText and QuestionOrder column to have the combined answers in a new column.

(I don't capture screenshots for above steps, you can find them from the pbix attached at bottom of this reply. The "Group by" step cannot be done by the GUI directly, you will need to modify the M code). 

The final result will be:

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[v-jingzhan-msft]

Hi @kenobi900 

Can you paste some dummy data of these three tables in table format? And what is your desired output? From the screenshot, it seems you already get the expected outcome of the first task?

Best Regards,
Jing