Forum Discussion
Helper VPivot Table in Excel to PowerBI
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"
8 Replies
- lbendlin
Super User
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
Yrstruly2021lbendlin 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
- 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=104129043494164133703&rtpof=true&sd=true
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. 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.
