How do I get a Folder Import to show all columns for the data?

[Anonymous]

When I import from a folder, the import only brings in the number of columns that pertains to the first dataset in the folder. In my scenario, that is only data ranging from January to April.

However, other files in the same folder have more columns (as shown below):

How can I get the folder import to show ALL of the columns even if they aren't in the first file?

[Anonymous]

HI @Anonymous,

In my opinion, I'd like to suggest sort your files by their column amount(DESC), then merge these files to one.

Table.Combine

Regards,

Xiaoxin Sheng

[Anonymous]

@Anonymous,

I've not heard of being able to sort by column amount. How would I go about doing this?

[Anonymous]

Hi @Anonymous,

>>I've not heard of being able to sort by column amount. How would I go about doing this?

Yes, current power query not exists this function. You need to manually write nested function to get column amount of table list.

Sample:

Custom= Table.AddColumn(#"Previous Step Name", "Column Amount", each List.Count(Table.ColumnNames(['Column stored tables'])))

Notice: please replace bold part based on your scenario.

Regards,

Xiaoxin Sheng

[Anonymous]

@Anonymous,

This definitely sounds like something I can use. Quick question: what do I put in the "Column stored tables" place? I'm new to creating custom Power Query code and appreciate you patiently walking through this with me.

[Anonymous · Accepted solution]

Hi @Anonymous,

You can add custom steps on your query table.

Steps:

1. Click fx button to generate new step.

2. Copy 'query name' after '=' character to replace #"Previous Step Name".
3. Use column name (stored table type) to replace 'Column stored tables'.

Regards,

Xiaoxin Sheng

[Anonymous]

@Anonymous, when you say column name and stored table type, where would I look to locate these things for this custom step? Is this the name of the new column, and what is a stored table type? Again, my lack of familiarity with M is showing here.

Thank you for your time!

[Anonymous]

HI @Anonymous,

>>when you say column name and stored table type, where would I look to locate these things for this custom step? 

Open advanced edit and you'll see original M query. ('let' xxxxx 'in' xxxxx)

#"xxxx" after 'in' keyword means current locate step, you can look for same name between 'let' and 'in' keyword.(step name is named with #"xxxx" at left side of '=' character)

>>Is this the name of the new column, and what is a stored table type? 

Power query tables allow use column to store table, record, list and normal value type. You can find out column store value shows as light brown 'table' and allow to expand.

Power Query - Overview and Learning

Regards,

Xiaoxin Sheng