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

[Anonymous]

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

10001	Attr1	natural and soft
10001	Attr2	put in auto mode
10001	Attr3	so natural!
10001	Attr4	get auto; get auto mode
10002	Attr1	bus at stop
10002	Attr2	natural icecream
10002	Attr3	boy is ingenius
10002	Attr4	more power
10003	Attr1	let me out!
10003	Attr2	promote goodwill
10003	Attr3	simple simon
10003	Attr4	auto model
10004	Attr1	matching color
10004	Attr2	natural color
10004	Attr3	natural essence
10004	Attr4	figure it!
10005	Attr1	god will
10005	Attr2	meet at bus stop
10005	Attr3	similar cases
10005	Attr4	rosetta stone
10006	Attr1	mud ringworm
10006	Attr2	helper columns
10006	Attr3	rainbow color
10006	Attr4	geneva convention
10007	Attr1	masked man
10007	Attr2	tenor voice
10007	Attr13	silica gel
10007	Attr4	natural smell
10008	Attr1	microcosm
10008	Attr2	guns of navarone
10008	Attr3	bloated cell
10008	Attr4	enigmatic man
10009	Attr11	cellular sky
10009	Attr2	natural color
10009	Attr3	color coated
10009	Attr4	ingenius of him!
10010	Attr1	ingenius body
10010	Attr12	maker chambers
10010	Attr3	old story
10010	Attr4	minor 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?

[edhans · Accepted solution]

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.

[Greg_Deckler]

You could do this easily in DAX using EXCEPT.