Pivot Table in Excel to PowerBI

[Yrstruly2021]

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"

[Yrstruly2021]

@lbendlin did you had a look at my data yet? https://docs.google.com/spreadsheets/d/1-FfCgSXpK5fNyH3AWyvwfUwBXT9WQqBb/edit?usp=sharing&ouid=10412... 

[lbendlin]

yes. I am struggling with point 3.  Is SAP Data your raw data?

[Yrstruly2021]

SAP Data will be the transactional data that is joined with dimensional data/tables. The SAP source data is then pivoted, as seen in 1st image above.

My challenge is, building the pivoted table in PowerBI from the SQL data source(extract from SQL), see:https://docs.google.com/spreadsheets/d/12RjkTzq1elO3bRzvu4LZ8DxHypZlzIsE/edit?usp=sharing&ouid=10412... 

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=10412... ) contains(SAP + Dimension tables) the tab Cash Flow where you can investigate how the values in Column G are compiled. The primary key contains duplicates, these can be filtered.

Data referential integrity is Many - 1. There can be multiple Account_Number linked to 1

ZTBR_TransactionCode.

Please advise on optimisation methods.

[lbendlin]

The raw data is usable. Your formulas however are not.  Please describe the business processes rather than the Excel formulas.  Power BI does not have an easy way to reference "same row, four columns to the left" and similar.

[Yrstruly2021]

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?

PBIX https://drive.google.com/file/d/1_XyrEdRUsQImyuDhapkA5fqcpXRGUjRV/view?usp=sharing 

[lbendlin]

Joining on not unique keys is of concern

Use composite keys.  If you can't, at the very minumum use single direction many-to-many relationships.

I do left joins to the Fact table and some inner join. Please advise.

Inner joins require clean data.  Yours has lots of gaps. If you do a left join you need to make sure that the left column does not have nulls.

You have lots of dimensions in your data model that seem to overlap in their meaning.  As you mentioned, whenever you have a 1-to-1 bidirectional relationship that is an indication that the tables should be merged.  I wouldn't necessarily call that denormalization, though.

the joins take a lot of memory

You don't have to do the joins / merges in Power Query.  Let the data model do the work for you.  Try to have join columns with low cardinality.

[Yrstruly2021]

I have brought in the Pivot table as an example of what the PowerBI report should look like, see:https://drive.google.com/file/d/1_XyrEdRUsQImyuDhapkA5fqcpXRGUjRV/view?usp=sharing .

My Fact Data, https://docs.google.com/spreadsheets/d/1mN6dGl4NY9N8yrXDpmx_CTJnZbxL61Zi/edit?usp=sharing&ouid=10412... 

Cash Flow tab in this file, https://docs.google.com/spreadsheets/d/1mN6dGl4NY9N8yrXDpmx_CTJnZbxL61Zi/edit?usp=sharing&ouid=10412... represents the final look with calculations as seen in my PBIX file. Source Data in the same file is my data in SQL DB. Please advise what will be the best way to get my data model/report aligned so that I can do the formulas in PowerBI using DAX as seen in the Cash Flow tab, column K.

[lbendlin]

What would be the quickest and most convenient way of turning an Excel report into PowerBI?

1.  Forget Excel

2. Describe your business problem

3. Provide data to Power Query in a usable format. Unpivot if needed

4. Implement the report in Power BI