Forum Discussion
ETL on Power BI
Hi Anonymous
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!
Hi Anonymous
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!