Transpose of table doesn't work.. Says too large data.

Question

Hi ExpertsSituation: I have about 60,000+ IDs and I have one of these tables, with each of these IDs in rows repeated about 30 times as key and values.&nbsp;Objective: To make into columns ID and corresponding 30+ Key and value columns ( unfortunately, they are not ordered, though most of the keys are same)What I planed is&nbsp;Step 1: Transpose entire table,Step 2: I&nbsp; delete duplicate columnsResult will be one ID per Row, with about 30+ Keys and values columnsStep 3: Then I move all the duplicate keys as column headers (per each ID)Step 4: Then I want to move Values under the key column headers, corresponding to each key&nbsp;Step 4: Delete unwanted key columns&nbsp;&nbsp;&nbsp;Question: I am not sure how to accomplish this from step 3 but2. I hit a road block on step 1, Power BI is complaining about transpose table saying "The type of the current preview value is too complex to display.. 😞&nbsp;Please can you help me 1. If my approach is right ? If right or wrong, please can you share best way forward&nbsp;2. How to do it? and any reference link would be appreciated.&nbsp;Thanks in advance

pijushroy · Answer

Please share data or sample data

acernz · Answer

PijushRoy&nbsp;Thanks a lot.I have created a small sample size with some issues as I see, but this data I run has more than 180000 rows, causing transpose to fail.1. I am thinking (subject to your advice, to have the data name and Data value (Col B &amp; C) in coloumns2. Data format should be Asia (DD/MM/YYYY)3. Please note that each ID has different set of Keys and values4. This type of data runs in about 180000+ rows with IDs more than 60,000 unique IDs which are repeated with multiple values.&nbsp;PLease adviceID numberData KeyData Value1Hire date23/05/20101GenderM1DOB&nbsp;1Phone121212323381Phone21231222431ApprovedYes1CostUSD 23002Hire dateMay 1 20182GenderF2DOB04/22/19772Phone112345678902Phone249876543212ApprovedYes2CostUSD 14002PositionStaff mgr2LocationBoulder2reports toGeorge Naunce2Employee typeContract3Hire date4th Mar 20163DeptQuality3CostUSD 15004Hire dateMar 6th 20184GenderMale4DOB08/26/19794Phone1-4Phone2-4ApprovedNo4CostUSD 50004PositionSales head4LocationDenver4reports to&nbsp;4Employee typeFull Time4Vehicle allowanceYes

pijushroy · Answer

Hi acerNZ&nbsp;Do you want to show your data like below?&nbsp;power query transpose&nbsp;want to show data in POWER BI visualization or want to make it in POWER QUERY?&nbsp;

acernz · Answer

Hi&nbsp;PijushRoy&nbsp;That is perfect but couple of things&nbsp;1. Date format has to DD/MM/YYYY2. What is the best practices in Power BI / Data.. to have blanks on empty fields are fill it with null?&nbsp;&nbsp;Thanks a lot

pijushroy · Answer

Please find the project file and try it.Change the source file.I am trying to sort out the date format requirement and let you know.If my answer solves your requirements, mark as SOLUTION.&nbsp;Find the pbix file -&nbsp;https://drive.google.com/file/d/1cZ4fRc957rS8jQghU7nJMGc3Onm9hqN-/view?usp=sharing&nbsp;&nbsp;

Forum Discussion

Transpose of table doesn't work.. Says too large data.

6 Replies

Related content

Help Transposing

Transpose data

Transpose Title Row ONLY

Pivot/ transpose

Transpose/ Transform Rows to Columns

ID number	Data Key	Data Value
1	Hire date	23/05/2010
1	Gender	M
1	DOB
1	Phone1	2121232338
1	Phone2	123122243
1	Approved	Yes
1	Cost	USD 2300
2	Hire date	May 1 2018
2	Gender	F
2	DOB	04/22/1977
2	Phone1	1234567890
2	Phone2	4987654321
2	Approved	Yes
2	Cost	USD 1400
2	Position	Staff mgr
2	Location	Boulder
2	reports to	George Naunce
2	Employee type	Contract
3	Hire date	4th Mar 2016
3	Dept	Quality
3	Cost	USD 1500
4	Hire date	Mar 6th 2018
4	Gender	Male
4	DOB	08/26/1979
4	Phone1	-
4	Phone2	-
4	Approved	No
4	Cost	USD 5000
4	Position	Sales head
4	Location	Denver
4	reports to
4	Employee type	Full Time
4	Vehicle allowance	Yes