Power Query - combine multiple Excel files - not seeing 'item' and 'kind'

[aikibart]

hi all

I'm using Power Query for first time after having watched some videos on Youtube.

I'm trying to load and combine a number of Excel files using 'From Folder' by following the steps in this video: https://www.youtube.com/watch?v=a7E29H5ZUmE, but when I reach the step at 2:25, my screen looks different.

In 2:25 he is expanding the data into 4 different columns: Name, Data, Item and Kind.

When I do this, I only get 'Name' and 'Data'.

Would anyone know what I am doing wrong?
Also, if you have any recommendations on how to learn Power Query that would be great too!

Thanks in advance for the help
Bart

 

[theBardd]

Mine is a bit different too, but seeing as you have the Data column, did you follow the steps that transformed that column?

 

[aikibart]

Yes i could further expand the data column, but as my excel files contain both multiple sheets and multiple named ranges, I was hoping to use the 'kind' field to filter out the named ranges. Would this be possible in another way?

 

[theBardd]

I just modified one of the files to have a second sheet, same format as the first, and that was pulled in as well. Is your data structured this way?

 

[theBardd]

What I am thinking is that if you just pull it all into Power Query, you can then use PQ's transformation functions to remove any columns/rows that you don't want. Without knowing the data, it is harder to be more specific.

 

[kkardynalski]

When you first click the expand icon, do you see "Load more" link right above the OK, Cancel buttons? If so, click that and you should see the rest of the columns.

I get this sometimes when I have both .xls and .xlsx files in the same folder.

Kathy

 

[aikibart]

Yes I can click 'Load more' but this doesn't give me more columns. All my files are .xls with multiple sheets and each sheet also has a 'FilterDatabase' named range (which you get when you apply a filter). I will try to play around a bit more by excluding all entries that contain 'FilterDatabase'.