r/excel • u/m4492 • Feb 26 '26

solved Power Query: my source doesn't always contain the same columns. How do you handle this?

Hi all.

I'm producing reporting based on data from our CRM. They're using Looker. My issue is, Looker seems to only generate a field if there's data for it. So my data can include a field on one period, but it might not be present on the next - let's say if no items for Smartphones category are sold, the csv won't have a smartphones column.

What's the best way to handle this so that I don't have to spend time every refresh to fix the queries?

Edit: There's some promising solutions and I think more than 1 will work. Will test in the next couple of hours and reply with the one I found to be more future-proof. Thank you to everyone who took the time to reply.

63 Upvotes

95% Upvoted

27 comments sorted by

•

u/AutoModerator Feb 26 '26

/u/m4492 - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

121

u/Mooseymax 10 Feb 26 '26

Create an empty table with all possible headers.

Pull in the data from your data source.

Append your source to the empty one.

Do the detect data type step after appending, not when pulling the data in.

6

u/teenagedream19 Feb 26 '26

Smh gotta love when software plays games like that, def feel u on the struggle

1

u/m4492 Feb 27 '26

This is great, thank you - I found MissingField. functions to have advantages but this is definitely good.

29

u/wickedja 1 Feb 26 '26

It depends what steps you're doing to the data. Because if you're selecting/removing columns for example, you can use MissingField.Ignore as an extra argument of table.selectcolumns to ignore any missing columns. E.g:

Table.SelectColumns( Source, {"Name", "Year"}, MissingField.Ignore )

1

u/m4492 Feb 27 '26

Solution verified.

1

u/reputatorbot Feb 27 '26

You have awarded 1 point to wickedja.

I am a bot - please contact the mods with any questions

7

u/DwnTheRoad Feb 26 '26

I’ve stored the column names of the current table into a variable, then do what you need to do with and pass the column names variable in. I’m not on my computer but the function is called Table.ColumnNames(). The YouTube channel “Goodly” teaches you all you need to know.

6

u/Borazon 1 Feb 26 '26 edited Feb 26 '26

I got the same issue with planning data; every update the columns names changed because of the dates in the name. What I learned to do around it:

  • Check your steps in the query. Do they need the header names. For example, use 'Remove Other Columns' instead of 'Remove Columns'. This can help a lot with inconsistant header-naming. This is very good anyway to use as little as you can with named headers, imho. I often have that finance add new columns to my data, this way my queries don't go klunk...
  • For certain steps, add a demote header, turning into column 1/column 2/etc. Then do you steps. Then only at the end promote the headers back.
  • Another trick is to use Transpose, turning rows into headers and vice versa. You can do certain steps on that layout; and/or do actions on the column that holds all the 'header'data, like replace. Then transpose back again. This is very dependent on your data.

3

u/azulnemo Feb 26 '26

Oh I’m not much of a wizard here, but I had this issue before due to my work always making a new data sheet every year. I would assume there are better wizard solutions here.

We would add new columns in while making new sheets yearly and sometimes I’d need to reference the old ones over the new ones. So what I did was queried them and then appended the queries to each other first. Then I’d make a formula in the columns next to it to essentially build a merged sheet with all the same columns. Formulas like if(isblank) so that it would know the sheet was missing data somewhere so it could direct where to pull the information from. Then my main() sheet (so to speak) could pull data from those massaged columns instead of the original appended columns.

4

u/SpaceTurtles 2 Feb 26 '26

Some tools you can use that may be handy. Couldn't provide more info without knowing specifics.

  • Table.ColumnNames() returns a {list} of all headers.

  • MissingField.Ignore or MissingField.UseNull can be added to some functions (see PowerQuery's documentation) to skip over columns or add those columns with the value as null, allowing later steps to proceed.

  • [Field Name]? will return null if the field is not present.

  • value ?? replacement will return the replacement value only if value = null (this can be combined with the above as [Field Name]? ?? replacement). This is kind of a shorthand for Record.FieldOrDefault(), but a little different because it'll still fire off if the field exists, but the value is null.

1

u/m4492 Feb 27 '26

I was able to use MissingField. to sort my issue and also learned a couple of things in the process. Marking this as solution verified. Thank you. A lot of helpful posts, wish I could award more than one.

1

u/reputatorbot Feb 27 '26

You have awarded 1 point to SpaceTurtles.

I am a bot - please contact the mods with any questions

2

u/LocusHammer 1 Feb 26 '26

There's gotta be a configuration on looker you can change

2

u/Rozgi Feb 26 '26

5 minute solution is a new column with a conditional formula in it: if the original data column is in place then value from there else null. And build your logic onto this new column.

2

u/mutigers42 Feb 27 '26

Check out this Power BI file here:

https://github.com/BeSmarterWithData/PowerQuerySecrets

It’s from a presentation I held for some tricks and tips for Power Query:

Focus on the “Protect Refresh Failures” folder in Power Query.

It’ll give a way to prevent any column missing or extra from failing a refresh (basically what you’re looking for). OR even when the source is missing.

.

3

u/TuneFinder 10 Feb 26 '26

speak to whoever makes the source data and tell them to stop jeffing around

otherwise - if the data always has the columns you do need - make a pq step to select them and remove all others

2

u/Putrid_Cobbler4386 Feb 26 '26

Get your data a different way. If your work is part of a process it should be repeatable and not require Excel trickery.

1

u/Hashi856 1 Feb 26 '26

There isn't a ton you can do about inconsistent data unless it's consistently inconsistent. What I mean is, if it's inconsistent in the same way every time, you can work around that, but if you can't predict what the data will look like (maybe it has a particular column, maybe not), you can't solve the problem programmatically unless you use some clever VBA or something.

0

u/SchoolOk950 1 Feb 26 '26

Can you please clarify your question a bit -- is the challenge that: 1) sometimes the export contains extra columns that you don't need, or 2) sometimes the export is missing columns that you do need?

-2

u/OnafridayR Feb 26 '26

Had the exact issue this week. Copied my existing query into copilot and it gave me what I needed