Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Seth77
Frequent Visitor

Manual data entry

I'm somewhat of a novice so I apologize ahead of time! 🙂  We are exporting data from our ERP system as it relates to revenue numbers.  I'm then able to import this data into Power BI Desktop and create some useful dashboard for our organization.  One of the things that's missing is projections.  In our ERP system there are no fields available for this data so it is a manual process to enter it into the same spreadsheet as the exported revenue data.  Since the projection data is static is there a way to have it predefined (enter it one time) within the Power BI Desktop so that the next time the revenue export happens and I refresh my dashboard the static projection data always stays.  I hope that makes sense.  Thanks so much for any help!

1 ACCEPTED SOLUTION

Hi, @Seth77 

 

Based on your description, I created some sample data to reproduce your scenario.

Sheet1(data in Excel):

i1.png

Sheet2(static data entered by 'Enter Data'):

i2.png

i3.png

 

Then you may go to 'Query Editor', create an index column for 'Sheet1' and 'Sheet2'.

i4.png

 

Then you need to go to 'Home' ribbon, click 'Merge Queries', set as below.

i5.png

 

i6.png

 

Finally you need to expenad the column to get the result. You may remove the index column if you don't want to load it into the model.

i7.png

 

Result:

i8.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

View solution in original post

9 REPLIES 9
v-alq-msft
Community Support
Community Support

Hi, @Seth77 

 

I'd like to suggest you use Enter Data to input the static projection data into Power BI. You can copy portions of a workbook or web page, then paste it into Power BI Desktop. Then you may create a new query which connects to the data from ERP system. When the report is refreshed, the static projection data will stay and the ERP data will be updated.

 

 

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Allan, I attempted your solution by using the Enter Data feature and created another table.  I'm just not sure how merging the data works.  I just basically want to add the columns and rows from sheet 2 (static data) to sheet 1 (daily exported data).

Seth, you will need to either join sheet 2 to sheet 1 in Power Query, or create relationships that join the tables. It's easier to provide a solution when we can see your data structure. Please upload screenshots if possible.

 

Allan's solution is an alternative to linking a Power BI table to the spreadsheet, but it still requires joining the two tables. The advantage of linking to the spreadsheet (vs. the Enter Data feature) is that you can easily manage the projection data in Excel (with the ability to filter, create formulas, have multiple users maintain the data, etc.). The Power BI grid that allows you to manually enter data is quite limited in functionality, and is best suited for smaller, static data sets.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Here's a small portion of the 2 sheets.  I'm just trying to add sheet 2 (static data) to sheet 1 (export data).  I'm sure this is simple again I'm very new to Power BI and really appreicate all the assistance!

 

 

 

Sheet1Sheet1Sheet2Sheet2

Hi, @Seth77 

 

Based on your description, I created some sample data to reproduce your scenario.

Sheet1(data in Excel):

i1.png

Sheet2(static data entered by 'Enter Data'):

i2.png

i3.png

 

Then you may go to 'Query Editor', create an index column for 'Sheet1' and 'Sheet2'.

i4.png

 

Then you need to go to 'Home' ribbon, click 'Merge Queries', set as below.

i5.png

 

i6.png

 

Finally you need to expenad the column to get the result. You may remove the index column if you don't want to load it into the model.

i7.png

 

Result:

i8.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Thank you, greatly appreciate all your help!

DataInsights
Super User
Super User

Enter the projection data in a separate spreadsheet, and import that spreadsheet into Power BI Desktop. Name this query "Forecast".

 

Import the ERP export into Power BI Desktop. Name this query "Actuals". This query will be refreshed each time a new ERP export is generated.

 

Create a relationship between Forecast and Actuals, and create the visuals that you want.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




But if I have that data in a separate spreadsheet then the data isn't linked to the other data anymore is it?  Along with projections I'm also looking to have historical data shown.  Which again is static but it has to all be linked with the current data for it to make sense.  Sorry if I'm using the wrong terminology.

The data would be linked in Power BI using common dimensions (e.g., Account, Dept, Year, Month). Each flat file would have the common dimensions, along with amounts. Each flat file would be a separate query in Power BI. Can you attach a snapshot of each of the three flat files so I can better understand your data (anonymize your data)?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.