cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sifar786 Member
Member

Remove specific columns from a multi-column table based on another single-column table

Hi,

 

I need to remove specific unwanted columns from the below DataExtract Table after unpivoting it's Attribute columns to reduce its huge size. The problem is that everytime a new DataExtract file will be imported and it may have different Attribute column names everytime.

 

DataExtract Table: (pivoted)

 

ItemID    Attr1                       Attr2                        Attr3                      Attr4

10001      natural and soft     put in auto mode      so natural!               get auto; get auto mode

10002      bus at stop             natural icecream       boy is ingenius        more power

10003      let me out!             promote goodwill     simple simon           auto model

10004      matching color      natural color              natural essence       figure it!

10005      god will                  meet at bus stop       similar cases            rosetta stone

10006      mud ringworm       helper columns         rainbow color          geneva convention

10007      masked man          tenor voice                silica gel                   natural smell

10008      microcosm             guns of navarone      bloated cell              enigmatic man

10009      cellular sky             natural color              color coated            ingenius of him!

10010      ingenius body        maker chambers       old story                  minor changes

 

 

DataExtract Table: (unpivoted)

 

ItemID    AttributeName    AttributeValue

10001Attr1natural and soft
10001Attr2put in auto mode
10001Attr3so natural!
10001Attr4get auto; get auto mode
10002Attr1bus at stop
10002Attr2natural icecream
10002Attr3boy is ingenius
10002Attr4more power
10003Attr1let me out!
10003Attr2promote goodwill
10003Attr3simple simon
10003Attr4auto model
10004Attr1matching color
10004Attr2natural color
10004Attr3natural essence
10004Attr4figure it!
10005Attr1god will
10005Attr2meet at bus stop
10005Attr3similar cases
10005Attr4rosetta stone
10006Attr1mud ringworm
10006Attr2helper columns
10006Attr3rainbow color
10006Attr4geneva convention
10007Attr1masked man
10007Attr2tenor voice
10007Attr13silica gel
10007Attr4natural smell
10008Attr1microcosm
10008Attr2guns of navarone
10008Attr3bloated cell
10008Attr4enigmatic man
10009Attr11cellular sky
10009Attr2natural color
10009Attr3color coated
10009Attr4ingenius of him!
10010Attr1ingenius body
10010Attr12maker chambers
10010Attr3old story
10010Attr4minor changes

 

I have created an Unwanted Table of Attribute columns that i will manually add unwanted columns in it and then load it to Power Query. I need to use it on the DataExtract Table to remove or filter those unwanted Attribute columns.

 

Unwanted Table:

 

Attribute

Attr11

Attr12

Attr13

 

How can this be done in PowerQuery for Excel?

 

1 ACCEPTED SOLUTION

Accepted Solutions
edhans Super Contributor
Super Contributor

Re: Remove specific columns from a multi-column table based on another single-column table

In Power Query, do this:

  • Merge your main data table with your UNWANTED data. In the merge type, choose Left Anti. It will only keep rows that do not exist in the unwanted data.
  • Then delete the column where the merge occurs in the next step. No need to expand anything as they are all nulls.

Is that what you want? If not, give a clearer example of the expected outcome.

View solution in original post

2 REPLIES 2
Super User
Super User

Re: Remove specific columns from a multi-column table based on another single-column table

You could do this easily in DAX using EXCEPT.

I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

edhans Super Contributor
Super Contributor

Re: Remove specific columns from a multi-column table based on another single-column table

In Power Query, do this:

  • Merge your main data table with your UNWANTED data. In the merge type, choose Left Anti. It will only keep rows that do not exist in the unwanted data.
  • Then delete the column where the merge occurs in the next step. No need to expand anything as they are all nulls.

Is that what you want? If not, give a clearer example of the expected outcome.

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)