Forum Discussion
Right data structure for Inventory Forecast data
Hi Folks,
Power BI newBie here. I current use Excel as a data source to merge 10 multiple files with same headers in to one table, using power query in PBI. Since the data is forecast data, every month a new excel file is created and I create a new column at the end of table manually, to all 11 files and then run power query to merge in to one table in PBI. With above process, i would be retrospectively editing all the past files to have same header names and then run power query to merge in to single table.
Is there a better way to automate this process without manually adding columns to all files?
Thankyou in advance!
- Use “long format” data: Instead of adding new columns each month, add new rows with a ForecastMonth column.
Store all files in one folder: Use Power BI’s Folder connector to auto-merge them.
Automate with Power Query: Clean one sample file, and apply the same steps to all others.
Optional: Extract month from filename if needed.
No more manual edits—just drop the new file in the folder and refresh Power BI
5 Replies
Hi CK_92
Please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind (which columns to use, merge and why?) . You may post a link to Excel stored in the cloud. Please read this sticky post - https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
- Use “long format” data: Instead of adding new columns each month, add new rows with a ForecastMonth column.
Store all files in one folder: Use Power BI’s Folder connector to auto-merge them.
Automate with Power Query: Clean one sample file, and apply the same steps to all others.
Optional: Extract month from filename if needed.
No more manual edits—just drop the new file in the folder and refresh Power BIHi CK_92,
Thank you for reaching out to the Microsoft Fabric Community Forum.
To help us troubleshoot without exposing sensitive data, could you try creating a small mock dataset that follows the same column names, data types, and general patterns as your real data, but with all values replaced or randomized? Even 5–10 sample rows are often enough for us to test.
Here is a guide you can use to create and share such data safely:
Get samples for Power BI - Power BI | Microsoft Learn
If creating a mock dataset isn’t possible, another option is to share:- The structure/schema of your tables (column names + data types only)
- The exact DAX formulas or Power Query steps you’ve tried
- A screenshot of the error messages you’re encountering
With that information, we can try to reproduce the issue in our own test environment and work toward a solution.
Thank you.Hi CK_92,
We haven’t received a response with the requested sample data to help troubleshoot your issue effectively.
To proceed further, please share a minimal and clean sample dataset (not a screenshot) along with the expected output. This helps the community understand and resolve your question faster.
Thank you.Hi CK_92,
Please share sample data relevant to this thread and let us know the expected outcome, as this will help us reproduce the scenario more easily.
Thank you.
