Forum Discussion

johnlockard's avatar
johnlockard
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's avatar
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's avatar
    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's avatar
      SergeiBaklan
      Diamond Contributor
      Jul 21, 2022

      johnlockard 

      You have 6 Rename Columns steps, what exactly you are doing here?

      • johnlockard's avatar
        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"}})
  • johnlockard's avatar
    johnlockard
    Copper Contributor
    Jul 21, 2022

    Martin_Weiss Thanks for the suggestion.  I should have used better column names in my example to not confuse with the default column names.  I do have Promoted Headers in my power query and I see  this same behavior.

     

    I also have other Renamed Columns as wells as added columns in my Power Query. I'll have to try to figure out the combination that causes this behavior.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jul 21, 2022

      johnlockard 

      The question is are

      Column1, Column3, Column2

      part of csv file and what are your settings here

      • johnlockard's avatar
        johnlockard
        Copper Contributor
        Jul 21, 2022
        Sorry for the confusion on the headers. I should have had the same CSV file specify a better row 1 like the following:

        Header1, Header2, Header3
        Find_This, This_2, This_3
        Find_That, That_2, That 3

        The first row in the CSV has the headers and is promoted to be the headers for the Power Query.

        My option setting is likely the default: Detect column types and headers for unstructured sources according to each file's settings

Resources