How to best analyze/treat 1:1 relationships

[Otso]

I have 100+ tables with 1:1 relationships, each with the same identifier, and each with about 400,000 rows (Soon to be million+, Excels natural limit).

I will need to analyze the data, and planned to use PowerPivot. Unfortunately PowerPivot doesn't support 1:1 relationships, so I tried to flatten/merge all the tables, but the process quickly became very time consuming.

I am now trying to use Power BI, as it supports 1:1, and have very easily been able to connect the tables. Unfortunately I'm now constrained by Power BIs inability to export data back into Excel (increadibly low limit), PBIs inability to research the data internally (sampling size of mere 3,500, no ability to build Excel like solutions), while PowerBi Connect is forcing me to create measures for each thing I want to analyze. In addition, the numbers coming back via Connect are treated as text by Excel.

What is the best way to analyze and manipulate a large number of 1:1 tables (I'm not looking for pretty graphs only)?

[v-caliao-msft]

Hi @Otso,

For Power BI desktop and Power BI services, if you have a visual that has too many rows, then we can only export some sample data, this is the limitation in Power BI. This is a known issue, please vote the idea: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/9609906-export-powerbi-desktop-to...

So currently, you requirement cannot be down in Power BI, to analyze and manipulate a large number of 1:1 tables, you could use SQL Server database.

Regards,

Charlie Liao