Deleting Columns after imported / cost of

Power Query
1

After working for months on a project, I know now that there are columns in many tables that I do not need them.

What is the best way to clean up the model with the intention of:

  • Save time / resourses loading the data
  • Iincrease the performance of my model by having an smaller data set.

Thanks, Ezequiel

2

@ezenunez ,

Three suggestions for you here:

  1. instead of removing the columns you don’t need, select Choose Columns and uncheck the ones you don’t need. It accomplishes the same thing as deleting them, but if you need to bring any of them back it’s much easier to do (you can just recheck them on the list rather than having to revise the M code directly in the advanced editor)

  2. do step #1 as early in your Power Query steps as possible. This will save time transforming columns that you don’t need.

  3. if you data is coming from a source over which you have some control (e.g., a corporate data warehouse), have those columns removed as far “upstream” as possible, ideally before they hit Power Query at all.

I hope this is helpful.

– Brian

1 Like
3

Thanks Brian,
I am sorry but I am not following you.
Next time I am importing a table I will be more carefully and not include all the columns.

When you say, “uncheck” the column I do not need, this is at the moment of importing the table, correct?

My only option now are:
A - Delete column
B - import the tables again with the only the columns need it and re-stablish the relationships in my model

Is this correct?

4

Hi @ezenunez. No, it’s not when initially loading the table, but rather in Power Query once the table is loaded. As @BrianJ said, do this as early in your Power Query transformations as possible (e.g., before any column name changes, data type changes, etc.).
Greg

5

because I work with a large number of visual learners, here’s some screenshots that might help:

  1. In the Power Query Editor, you can use the “Choose Columns” button:
    image

  2. any column with a checkmark in the box will remain in your report (any unchecked columns will be removed from that point going forward) In this example I am removing Priority and Initial Req. Date because they aren’t needed for this table:
    image

  3. if I later change my mind, I just have to locate the step in my applied steps table, and edit the selection by clicking on the gear icon at the right to open the checklist up again:
    image

And I agree with Brian and Greg - remove the extra columns as soon as possible to help speed things up. :slight_smile:

6

@ezenunez ,

Sorry if my prior post was unclear. If you are not able to eliminate the unnecessary columns “upstream” before they hit Power Query, there are two ways to get rid of them:

image

  1. Select the columns you want to remove and then click “Remove Columns” (or if you are removing most of the columns, select the ones you wish to keep and then click “Remove Other Columns”

image

  1. The preferred approach (highlighted in @Greg’s outstanding series as a best practice) is to click “Choose Columns” and then uncheck the ones you wish to delete, per below:

image

The great things about this approach are that it’s visual, searchable and if you change you mind down the road, you just open this dialog box again and recheck the ones you now wish to retain.

Hopefully, that clarifies things.

  • Brian
1 Like
7

Thanks Brian and Heather for your help!
I have selected only the columns that I need it and I see the difference now.

Regards, Ezequiel

1 Like