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 guys,
I'm here once again.
I followed your advice and I bought the book "Collect, Combine, and Transform Data Using Power Query in Excel and Power BI".
Very helpful and thank you all for suggest.
Now I did something on PBI with that xls files.
I little modified the XLS files to be easyer for me and for PBI to manage them.
But now I'm here becouse I stille have some problems.
Here you can find XLS Files and PBIX files.
google drive file
I don't know if I use the best way to reach my goal but I'm confident that this is a good path.
if you download and open the PBIX file you will see that all it's ok.
But I have a little problem that I didn't resolve.
I repeat that my goal is to have data from different XLS files in the folder.
XLS are all made with same columns from 1 to AAA COSTS. After that we can have different number of columns.
and the same is obviously for rows.
The problem is that I need to sum AAA CONTRACT, AAA BUDGET and AAA COSTS also if inside the colums at the right of AAA COSTS will have no data.
infact if you look at XLS files and data inside PBI dashboard you will see that I loose some data when I applied the step "Unpivoted" inside the Power Query.
To see what data I'm missing, you need to keep an eye on the Subcat_Description column where: for both the XLS file TSC_LIM and the TSC_NOL file, you'll see that the row corresponding to the "var1" entry is missing. In fact, if you notice, in correspondence with this row, in the columns major than AAA COSTS there are no numeric values.
Now I don't know how to proceed, how to make PBI take the lines in question into account.
I kyndly ask if someone of you can help me to modify the Power Query steps or suggest me the correct way to resolve my problem.
Thank you in advance.
Best
Marco
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 Marco_88
Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.