Remove columns changes values to null

[RickSchultz]

Hi!  I have a very wide table ("Visits").  I want to make a second table that includes only a few of the columns from that table.

I create a new table ("Visits_ROI") using the Visits table as the source.  I then remove most of the columns, and two of the columns I want to keep have all their values change to 'null'.  

Why would that happen?  How can I fix?  Alternately, thoughts on how to troubleshoot something like this efficiently?

[RickSchultz]

It's a decent size (9 original tables, 4 calendar tables, 4 calculated tables) and the data is pretty sensitive, unfortunately.  Yeah, I've never seen this before.  If I take away the "remove columns" step, all good.  Also fine if there are only one or two columns removed.  Breaks when I have about 8-10 columns removed; none of them are id columns (primary or foreign) and they all have their own nulls, so nothing should depend on them.  Very odd.

[DOLEARY85]

Can you try removing the ones you don't need 1 by 1 to see if it's a particular column?

[RickSchultz]

That's what I'm down to.  Was hoping someone had a more efficient approach - it keeps wanting to refresh periodically, which leads to a 2-3 minute pause each time...

[DOLEARY85]

I've never seen the issue before and without the PBIX file it's hard to diagnose. If you do find out the issue i'd be interested to know what caused it, sorry I can't be more help 

[RickSchultz]

Thank you.  It helps just to talk with someone about it.  I'll certainly post my resolution here once it shows up.

[RickSchultz · Accepted solution]

Fixed; not sure how to explain how it arose, though.

When I looked at the query output in Power Query, it looked like all the data was there at the beginning of the query (source) but that, after a given point (when I removed the fields) the two columns lost their data (all null).  This is a query that has worked for many months.

So, I removed the step which took out the columns, stepped through a few more steps, and again, the columns went from full to null.  This made me think something was a problem underneath.

So, I went back to the "source" step in Power Query, and THIS time, the interface said the data was 7 days old - did I want to refresh?  OK, yes, lets try that.  NOW, and only now, the data in the "Source" step shows the 2 suspect columns as null to start with.  This means that the data must be missing from the underlying query.

So, i go back to that query - and the data is there!  Both columns!  Huh?  For grins, I refresh the preview, and NOW the columns are null.  Problem must be the query (was pulling from an Athena datalake).

Went into the query (which I had modified late last week) and, sure enough, there was an error in the link that brought in those two columns.  Fixed that error, and now everything works fine.

So, the ultimate problem was in the underlying query; Power BI just didn't do a great job helping me figure that out.

Thanks for your help, DOLEARY85.  Your feedback was very valuable in understanding that this wasn't a normal issue, and encouraged me to keep digging.

[DOLEARY85]

Hi,

are the 2 columns that change to null custom columns or are they all direct from the source?

[RickSchultz]

Direct from the source - that's what's odd.

[DOLEARY85]

Very strange, can't say i've seen this before. Any chance I can see the PBIX file?