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.
Marco_88 I would be surprised to see anyone document all those steps for you, so instead I highly recommend getting Gil Raviv's book called, "Collect, Combine, and Transform Data Using Power Query in Excel and Power BI." It's one of the most useful resources I've bought to guide on accomplishing what you're looking to.
