cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BryceBicknell
Frequent Visitor

Expression Error Column Not Found When Refreshing Data

Hi all,

 

I receive data on a daily basis in the below format (excel csv), the dates cap at 1815 days with sales by store underneath the dates begining in column H (last column is BRB) and because the columns are capped the dates continue to change every day and since these have been promoted to headers and the unpivoted when I save the new data file into the folder and refresh my PBI report it throws up the Expression Error: Column xxx of the table wasn't found. Is there a work around to solve this issue?

 

Sample Data:

BryceBicknell_1-1609818040558.png

 

How I use my data in end report:

BryceBicknell_2-1609818651918.png

 

Any help is greatly appreciated!

 

1 ACCEPTED SOLUTION
ImkeF
Super User II
Super User II

Hi @BryceBicknell ,

sorry, didn't realize there was still the changed type step in it.

Please use this new download instead.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

7 REPLIES 7
ImkeF
Super User II
Super User II

Hi @BryceBicknell ,

sorry, didn't realize there was still the changed type step in it.

Please use this new download instead.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

ImkeF
Super User II
Super User II

Thanks, please see file attached

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hi ImkeF,

 

Unfortunately I have the same error when refreshing as the first date "column H" has changed, and a new date has been added at the end "column BRB". 

ImkeF
Super User II
Super User II

Sounds as a misunderstanding to me.
Could you please create a file with sample data and post link here?

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Yes see below 

 

Link 

ImkeF
Super User II
Super User II

Hi @BryceBicknell ,

there are several ways to do it, but the easiest way would probably be to check columns A-G and use "Unpivot Other columns" instead of you current unpivot that selects the other columns.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hi @ImkeF,

 

Unfortunately that has the same error, probably because I have promoted the dates row as headers then unpivoted this is bringing the issue of the column not being found as that date no longer exists in the new data but on the oposite end a new date is added, I should have shown the opposite end of the raw data as the rows shown in the first image are blank (dates the stores weren't open) but there are actually values per store see below. 

 

BryceBicknell_0-1609834160207.png

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors