cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
clachi80
Frequent Visitor

refer to column with wildcard

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? 

1 ACCEPTED SOLUTION
AlexisOlson
Resident Rockstar
Resident Rockstar

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 _
)

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User III
Super User III

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
AlexisOlson
Resident Rockstar
Resident Rockstar

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 _
)

View solution in original post

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?

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors