Forum Discussion

Copper Contributor

Jul 21, 2022

Power Query to CSV column order changes effects references to Power Query

I have what I consider a very bad behavior for references to a Power query when the order of the CSV files the query is based on changes from one refresh to the next, the references to the power quer...

excel

Martin_Weiss

Bronze Contributor

Jul 21, 2022

Hi johnlockard

I tried to replicate your problem but it works fine on my side. The XLOOKUP does not change after a refresh.

But I have a suspect: Do you use the step "Promote Headers" in your query?

As you can see, for the second file, the header names from the CSV are not identical with the current "real" header names.

If you do use "Use First Row as Headers", this will change the headers to whatever comes from your CSV and your formula should work fine:

johnlockard

Copper Contributor

Jul 21, 2022

Martin_Weiss Below is my full list of Power Query Steps that produce the behavior. I tried an enhanced test that added renamed columns and I have not added complexity that reproduces the behavior I'm experiencing with the steps below.

SergeiBaklan
Diamond Contributor
Jul 21, 2022
johnlockard

You have 6 Rename Columns steps, what exactly you are doing here?
- johnlockard
  Copper Contributor
  Jul 21, 2022
  The steps that show as renamed columns are actually creating new columns by parsing out data from another column.
  
  Table.RenameColumns(#"DB Type Delimiters",{{"Text Between Delimiters", "Type"}})
  - SergeiBaklan
    Diamond Contributor
    Jul 21, 2022
    johnlockard
    
    That's better to have entire sample file. If reproduce steps from your initial post - all woks, no problems.