Could use some help editing Excel data to fit to Power BI reports

[IMK]

I would need some with sorting and editing Excel report in correct form for Power BI reports.

Below I have an screen capture of the Excel report in a way we get the report from our ERP.

This report is sales by product group and products. This report stacks those individual product group reports like the image above shows. There is numbers for the first product group, then one row in between and then the same for second product group.

If we start to manually separate this report, it takes a lot of time. Is it possible to edit this Excel report in Power BI Desktop in a way that could be used in power BI reports? If so, could someone help a bit so that i could find a way to accomplish this?

The last row in every product group is "Product group total". We do not need this row because we can calculate that info from other row's in every product group.

[dbrown]

Hi,

The simplest thing to do will be to use Power Query to clean the report. The sheet used to clean the report can then house the query used to clean future data dumps.

Could you attach the actual excel file so I can send you the cleaned up file and the query code?

[IMK]

I was planning to attach the file but didn't find a way to do that.. How do I send the file?

[dbrown]

You can send a dropbox link I believe

[IMK]

Hi

There was a little correction to the file. When we get the report out of our ERP, in row 2, there is an orphan "Product group total" row. This same row is on every Excel we get out of our ERP and it can be deleted. Below is a link to the sample excel file.

https://www.dropbox.com/s/4fd61pd5rpeob68/Product%20group%20sales%20example.xlsx?dl=0

[Eric_Zhang]

@IMK

The report in excel looks like a measure visualization in power bi. What is your dataset like? Instead of importing the report, I'd use a measure in power bi if the dataset is as below.

[IMK]

I apologize but I dont quite understand what you mean by dataset? I am used to with 0365 but fairly new with Power BI or data crunching...

We get the report from our ERP like it is behind dropbox link. I would like to get it in a way as shown in your first image somehow automatically so that I can use it in Power BI. This should happen automatically, for example so that the change in the Excel report happens in Power BI desktop.

In Power BI, we would like to create reports about total sales in different product group's, maybe list top ten/twenty sold items in quantity and something like that.

But the goal at the moment is to get the report turned to look like in your example Excel screen capture so that it can be used in Power BI. If it can be accomplished in Power BI Desktop, it might be the easiest way for the user, which is not a tech guy but ecomonic guy. This way, if I have got this correctly, I can create the Excel data form change once and after that, this number guy, actually she, can just import new reports from ERP and just do "Refresh" and "Publish". Did this come out in undestandable way?

[djnww]

Hi @IMK,

All reporting tools (Power BI/Tableau/QLIK etc) require data to undergo 'data preparation'. This means the data needs to be altered to be in a specific format. Do you have experience in creating Pivot Tables in Excel ? I suggest that there is where you start. Pivot Tables require data to be in a specific format. This format will work with Power BI.

Also, if I were you, I would request that the ERP vendors send you the data in a CSV format. Almost all ERP systems provide for this capability. In SAP, you have the option of selecting the 'file type'. You should then be able to input this automatically into Power BI.

Regards,

DJ

[IMK]

Hi

I know what Pivot Tables are in Excel. But the problem here is that how can I get this dataset

to look like this

@Eric_Zhang

Could you please help me to find out how you managed that data preparation?

Or if it would be possible to create a Power BI Desktop file where this data preparation settings is done? If I could manage either, i could get forward with this thing..