Forum Discussion
Looping through table to create another one
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.
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
4 Replies
Yes, in Power Query, select your PostalCode column, right-click in select Unpivot other columns.
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.
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
