Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have loaded a query from the web that has the last three weeks of price history. This history is updated weekly, so when a new week populates a column, the oldest week is kicked out. This update is giving me an expression error because the oldest week cannot be found on the table. The information I need is in a single cell of the latest price history, so older history can be ignored. The only solutions I have found so far is to manually remove the column in the editor which is something I do not want to continue to do.
Is there a way to completely ignore the error message and have the table update with the new week? Or is there a way to update the entire table so that I don't get an error at all?
I have screenshotted my issue below.
Solved! Go to Solution.
You need to unpivot that data so you are protected from column name changes for these columns. After unpivoting you will always have just two columns, and no need to update the code.
Thank you lbendlin! Your solution seems to have worked. My issue now is that when I refer to the single cell that has the price, I get an "#N/A" in cells that interact with it.
For example, Cell A2 has =(cell with price). Cell B2 has =VLOOKUP(A2,'FSC1'!H:K,4,TRUE). Cell B2 populates with "N/A" where previously if I simply type in the price manually it populates with the correct result. Any insight would be greatly appreciated again.
In Power Query you can use "Replace Errors", "Remove Errors", or the try...otherwise... construct to deal with these issues (which frankly should be solved further upstream, in Excel).
You need to unpivot that data so you are protected from column name changes for these columns. After unpivoting you will always have just two columns, and no need to update the code.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
105 | |
99 | |
98 | |
38 | |
37 |
User | Count |
---|---|
152 | |
120 | |
73 | |
72 | |
63 |