Building Index Column from Multiple Columns

[numberpro11]

Hello,

I'm a regular user of excel, but new to power query and power pivot. I'm using Excel 2010.

So I have a number of tables in this structure:

Region/Field Office/FY/Month/Metric

Each data table hold this same structure but with a different metric. So my thought was to build a custom index column using the first 4 columns.

Index = Region+Miami-Office+FY+Month. I used the & function without any spaces to concatenate so that it would look like this:

NorthMiami-Office2015Oct. This new column is called Index. Yes, I converted the merged columns into text type first.

Then I start with one table, then attempt to use the merge query function to bring in the other metrics. I select the Index columns on both tables, select Left Outer join. However, I see that the dialogue box is saying 'we were unable to determine how many matches the selection will return'.

When I click ok, it does construct the new 'table' column where I can select the columns to merge. However, when I chose the Metric to merge into the current table, I get this msg:

DataFormat.Error: Invalid cell value '#N/A'.

=========================================

I thought it might be extra spaces, but using the trim function does not appear to make a difference. I cleaned two Index columns and attempted a merge, but still got the same error msg.

Am I doing something wrong? How do we handle this type of error?

Alternatively, I thought of using the 'add index column' function but I'm not 100% sure that all the rows in each of the tables line up one to one. That is the Region/Field Office/FY/Month/Metric have the same names, but the sort order may be different. Is there a way to ensure that the sort is the same for each table and then I can simply add an index column starting from 1?

Again, I am a beginner to power query and would appreciate any suggestions!

 

[numberpro11]

Hello,

I am the original poster. I believe I've discovered the issue, but would like some more help.

When I closed and loaded the tables into Excel, what I noticed is that on the tables that did not merge successfully, the number of rows were drastically reduced. What I mean is that the each table should have had 1008 rows of data, but some tables had only 83.

I believe this may have happened due to two possible errors. First, I used unpivot to create those data tables. The second is that for some reason, the unpivot was successful but somehow the full data table did not load. Has anyone encountered this type of error? What can I do to verify the source of the problem?

Thanks!

 

[numberpro11]

Ok, I believe I've found the problem. Before performing the unpivot, I had to remove errors in the data columns. It's strange how I can stare at a problem for hours, and then post, here and suddenly it resolves itself. Funny.