cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Looping through table to create another one

Hi, I'm have a table which has been setup as follows:

 

PostCodeCompanyACompanyBCompanyCCompanyD
3015YesYesNoNo
3016NoYesNoYes
3017NoYesYesYes
3018NoNoYesYes

 

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.

CompanyPostCode
CompanyA3015
CompanyB3015
CompanyB3016
CompanyD3016
CompanyB3017
CompanyC3017
CompanyD3017
CompanyC3018
CompanyD3018

 

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.

1 ACCEPTED SOLUTION
Community Support
Community Support

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


6.PNG

7.PNG

Step2:

Filter the Value is "Yes"

9.PNG

Result:

8.PNG

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Community Support
Community Support

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


6.PNG

7.PNG

Step2:

Filter the Value is "Yes"

9.PNG

Result:

8.PNG

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Frequent Visitor

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.

Super User IV
Super User IV

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors