how to extract additional columns from xls files

[Anonymous]

hi

i have a pbix file that is based upon the use of xls files which are stored on a networklocation since 2018. There are 15 columns in these files which are extracted to the model. Since August 2022 5 additional columns were added to these files and i am trying to extract them as well. As a workaround i created a new query where i filtered all the xls files being created after July 2022. The challenge now is how to add these addional columns as merging is not an option and using the append option, will present the columns but it will give values = NULL. 

Hope you can help.

thnx

M

[PhilipTreacy]

Hi @Anonymous 

The problem is that these 5 new columns contain new data that wasn't in the old Excel files pre Aug 22. Is that correct?

So how do you intend to incorporate this data into the old tables?

There's only so much I can advise given that I haven't seen the data and more importantly don't know how you think these extra 5 columns can be incorporated into the old data.

You have 5 new columns of data.  How do you think that data can be squashed into the old file format of only 15 columns?

Am I missing something?

regards

Phil

[Anonymous]

hi Phil, you nailed it, spot on!!! the initial files contained only 15 columns and then at any given moment, the business decided to add 5 additional columms. And yes, these 5 new columns contain data which was not to be found in the previous files. Not that easy to get these columns in the model...

[PhilipTreacy]

Hi @Anonymous 

Well, you can get the new data into your model, but the columns will show null for any dates pre Aug 2.  There's no other way around it.

Phil

[Ashish_Mathur]

Hi,

Depending upon the process that you followed for getting data from all Excel files, in the Query Editor there should be step of Expand column.  Double click on that step and check the boxes of the columns which you want to bring over.  This is the process i follow for creating a single table from multiple Excel files - Power Query #08: Import Multiple Excel Sheets From Multiple Excel Workbooks - YouTube

[Anonymous]

hi Ashish, thanks for the swift response. Problem here is that if you start extracting the columns from the first file, there are only 15 columns. Only after 20220731 the business added 5 additional columns and extracting them is not possbile, that is why i created a new query with only files after aug. 2022 but adding these columns is still a challenge as merging will give incorrect data (used a key column to make join) and append will give the columns but they do not have a value (always NULL).

[Ashish_Mathur]

Hi,

Follow the steps in the YouTube video link which i shared with you in my previous post.

[PhilipTreacy]

Hi @Anonymous 

I'm not sure what you expect as a final result?

If you have 5 new columns in these newer files then how do you want them added to your existing table structure?

As you say, if merging isn't an option then you are left with an append, and as you also say, that will give you null values in the columns for dates before Aug 2022. 

But I don't see any alternative?  You don't have data for these columns pre Aug 22.

Phil

[Anonymous]

hi Phil, thanks for swift response! At first it seems an easy topic but it is not. The problem is that before aug 2022 these columns were not in the file, so no data/columns, and merging or append will not give the proper result. Will keep trying!

[PhilipTreacy]

Hi @Anonymous 

What is it you want as a final result?

As you've said, merging won't give you what you want, neither will an append.

They are the two options we have for sticking tables together, if neither is what you want, what do you want to see as your final table?

Phil

[Anonymous]

hi Phil,  the expected result should be that i can combine the 15 columns from the iniatial file with the 5 extra columns in the files since august 2022. Did a test with merge: result gives incorrect values, Append is giving NULL values. Tried to add the columns as suggested by Ashish as well but that is not working either. Do not know how to solve it, besides telling the business that we can only deliver these values if we only extract from august 2022, but am sure that is not what they want....