Forum Discussion
Read and manage different XLS in folder
Hi Marco_88
Based on my understanding of your requirements, I have listed the points below. Please take a look.It looks like you're losing some rows (like "var1") in Power BI because of how the subcontractor columns are being unpivoted. Basically, if a row has no values in those columns (the ones after "AAA COSTS"), Power BI might just drop it during the unpivot step.
To avoid that, try unpivoting the columns in a way that keeps all rows even the ones where everything is blank. One easy way is to use Unpivot Other Columns and make sure you're not accidentally filtering anything out. You can also replace the nulls with zeros afterward if that helps keep things clean.
This way, all your data stays intact, even if some rows have no subcontractor costs, and your dashboard reflects the full picture.
If I misunderstand your needs or you still have problems on it, please feel free to let us know.
Thanks.
Hi bchager
I was about going to say the same as well. OP is asking for a lot in a single post.
Hi Marco_88
When connecting to a folder, you'll see a list of all available files—including all file types and even temporary files. The Content column will have a double down-arrow icon next to its name. Clicking this icon prompts Power Query to automatically apply transformations, which might not always align with your specific needs. Note: Prior to clicking this column, make sure to select .xlsx files only and remove those with $ in the filename (temporary files).
Go to Transform Sample File. Any transformations applied there will automatically be applied to all Excel files in the folder. You can undo steps like promoted headers, add a concatenated column, select columns, or apply filters as needed.
Now, go back to Query1 and locate the Invoke Custom Function step. Modify each #"Transform File"([Content]) to try #"Transform File"([Content]) otherwise null. This change ensures that if the automatically created custom function returns an error when invoked, the row will return null instead of causing the query to fail.
danextian Good advice!
Marco_88 I also recommend using Copilot to help you accomplish what you're looking to in the Transform Sample File query. You can upload sample data and tell Copilot exactly what data transformation steps need to be applied using M code and it will write it for you. I've recently done the same on raw data that needed many transformations before being usable, and it's brilliant. You may have to tweak the code here and there or tell Copilot where it still doesn't look right, but it should ultimately get your data to where it needs to be.