Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, I'm have a table which has been setup as follows:
PostCode | CompanyA | CompanyB | CompanyC | CompanyD |
3015 | Yes | Yes | No | No |
3016 | No | Yes | No | Yes |
3017 | No | Yes | Yes | Yes |
3018 | No | No | Yes | Yes |
This unfortunately has company names as column headers and whether they operate or not in a particular postcode in thier own column.
To be able to work with it effectively, I want to transform it to the following where I loop through each row and column and add a row in the new table for each time a "Yes" is found, and then put the Company Name which is in the header and the postcode from the first column as the values.
Company | PostCode |
CompanyA | 3015 |
CompanyB | 3015 |
CompanyB | 3016 |
CompanyD | 3016 |
CompanyB | 3017 |
CompanyC | 3017 |
CompanyD | 3017 |
CompanyC | 3018 |
CompanyD | 3018 |
Is this possible? I'm having trouble understanding how to use loop type functions using M Query or Dax or anything in PowerBi.
Thanks for any advice people can provide.
Solved! Go to Solution.
hi, @jz7ssr
You can use Unpovit Function in Edit Queries and then filter Value is Yes as below:
Step1:
Select Column CompanyA, CompanyB, CompanyC, CompanyD then click Unpovit Columns
Step2:
Filter the Value is "Yes"
Result:
Best Regards,
Lin
hi, @jz7ssr
You can use Unpovit Function in Edit Queries and then filter Value is Yes as below:
Step1:
Select Column CompanyA, CompanyB, CompanyC, CompanyD then click Unpovit Columns
Step2:
Filter the Value is "Yes"
Result:
Best Regards,
Lin
Hi,
You un-pivot in the edit query window.
Go to the section "Unpivot; Turning Columns to Rows; Name, Values" in below link you will find the step by step process
http://radacad.com/pivot-and-unpivot-with-power-bi
Thankyou! This does it all in one click, I was trying to write a complicated looping routine.
Perfect thanks.
Yes, in Power Query, select your PostalCode column, right-click in select Unpivot other columns.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
75 | |
51 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |