Please assist.
What would be the quickest and most convenient way of turning an Excel report into PowerBI?
Here is the view of the Pivot in Excel:
Here a table/report is created in Excel containing the pivoted data:
Please note that column 11 is made up of combined columns, e.g “=-SUMIF(‘SAP Source’!C[-10];‘Cash Flow’!RC[-4];‘SAP Source’!C[-3])+334776”
The quickest and most convenient way to get some help from the forum members would be to provide a PBIX file of your efforts so far and/or the Excel data that you are using. Without providing these it’s difficult to help much more than the below.
Using a Matrix visual would most likely suit your needs.
I would suggest to look at the source output from SAP and export it to a folder and get the data from the folder.
I see a tab in the excel named sap source.
export the account master data and create a relating.
I go with Davie Joe
a sample data set would help to look at the problem
kind regards
Roger
you could have a look at the webinar ( link attached )
How To Create Profit And Loss (P&L) Statements In Power BI | Enterprise DNA
@DavieJoe did you had a look at my data yet? https://docs.google.com/spreadsheets/d/1-FfCgSXpK5fNyH3AWyvwfUwBXT9WQqBb/edit?usp=sharing&ouid=104129043494164133703&rtpof=true&sd=true
Anthony
I added a simple income statement, I have no knowledge of accounting .
I loaded the sap source data in power BI and transformed the data to get the report.
I you have the chart of account in SAP with the mapping you use in the pivot table, you can easily get the same report in power BI.
you simply create the link between the two based on the account#
see also the link for the zebra visual, this is specially designed for P&L reporting
kind regards
Roger
income statement.pbix (42.5 KB)
Thank you @Roger. The challenge i believe is getting the report more or less the same using the SQL source. Attached is some sample data from the SQL DB.
Sample B.xlsx (24.7 KB)
Please note from the excel data you can filter Account_Number(50203010) as an example.
The Matrix/Pivot feature in Powerbi will work. Now it is building the formulas like, e.g “=-SUMIF(‘SAP Source’!C[-10];‘Cash Flow’!RC[-4];‘SAP Source’!C[-3])+334776”
into PowerBI. So all of the referencing is shifting from Excel sheets to SQL table(s).
The data link(https://docs.google.com/spreadsheets/d/1-FfCgSXpK5fNyH3AWyvwfUwBXT9WQqBb/edit?usp=sharing&ouid=104129043494164133703&rtpof=true&sd=true) contains(SAP + Dimension tables) the tab Cash Flow where you can investigate how the values in Column G are compiled, see.
Would you be able to replicate the PowerBI report with this data, https://drive.google.com/file/d/17aOmG-Ynx-6U05wNNrHXJG7iywgCMiuy/view?usp=sharing ?
(1) The fact table is from SAP.
(2) Dimension Tables are named “B Masked”. Please note that none of the tables had primary keys, I had to bring them in myself. In the Bracs Mapping tab of this file, there is a column GCoA which is the Account Number. Then there is BRACS which is the Bracs(source) Account number. these numbers match the same columns in the Mapping tab of the Cash Flow Pivoted file.
(3) The Source Data is a combination and shorter version of the Mapping tab. Column Account match GCoA and BRACS Account match
matches BRACS in the Bracs mapping tab above of the B Masked file.
I noticed some similarities in the different tabs/tables so I denormalised some tabs/tables. e.g. Region Mapping and Entity Mapping in the same file I made as 1 table.
Joining on not unique keys is of concern.
(4) Cash Flow file, tab Sap Source is a pivoted table of the Source Data.
(5) The Cash Flow tab is the final reporting structure that must be replicated in power. Column “G” in this tab is the “Function” column in the Pivoted tab.
I do left joins to the Fact table and some inner join. Please advise.
The Function column that makes up the bulk of giving meaning to the figures, most of the detail of this column is left out when I do a join as seen in Imeta_Bracs_Roll_Up joined to Fact file with SQL code.
I brought in the Mapping table to connect the roll-up table to it as seen in Imeta_Bracs_Roll_Up joined to Bridge, this query takes. very long to run. Also, the joins take a lot of memory and some ETL/SSIS tasks may fail. What would you be bettering in this instance?
Anthony
I will take a look at it later this week, probably the weekend. Im very busy for the moment in finishing a project
hope this can wait so long
kind regards
Roger
