Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
RickSchultz
Helper II
Helper II

Remove columns changes values to null

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?

1 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.

View solution in original post

9 REPLIES 9
RickSchultz
Helper II
Helper II

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.

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

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...

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 

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

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
Super User
Super User

Hi,

 

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

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

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors