Looping through table to create another one

[jz7ssr]

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.

[v-lili6-msft · Accepted 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

[avknishokpbi]

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

[jz7ssr]

Thankyou!  This does it all in one click, I was trying to write a complicated looping routine.

Perfect thanks.

[Greg_Deckler]

Yes, in Power Query, select your PostalCode column, right-click in select Unpivot other columns.