refer to column with wildcard

[clachi80]

the source of my table is coming from excel files stored in the same folder. The table is based on the 1st excel where there is a column named 'Home 2020 price'. Then I add in the folder a 2nd file where the column is named 'Home 2021 price'. Power bi does not add the column 'Home 2021 price' because has a different name. I need to find a way so that power bi takes the input from column 'Home' & * & 'price' in each file and reports the output under the same colum 'Home price'. How can I use the wildcard in the advance editor? 

[Ashish_Mathur]

Hi,

You must also create another column for Year to know which year the price is for.  I would suggest that you follow the technique shown in this video to allend data from all files lying in a folder.  This will pick up each column from each file so you will have multiple price columns.  Thereafter, you should select all columns other than the price columns and select "Unpivot other columns".  Lastly, you may use Split columns to split the Year into another column.

[AlexisOlson · Accepted solution]

You can transform the column names to remove the year before you try to combine the tables.

Table.TransformColumnNames(
    #"Previous Step Name Goes Here",
    each if Text.StartsWith(_, "Home") and Text.EndsWith(_, "price")
         then "Home price"
         else _
)

[clachi80]

I was able to change the columns' name adding your code in a step in Transform File, but then I got the ole db or odbc error expression.error there weren't enough element  in the enumeration to complete the operation, when close and apply. What might be the reason for this error?

[clachi80]

I realized that the error was generated by one of the excel workbooks that miss 3 columns compares to the other workbooks