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

[acerNZ]

Hi Experts

Situation: 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. 

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 

Step 1: Transpose entire table,

Step 2: I  delete duplicate columns

Result will be one ID per Row, with about 30+ Keys and values columns

Step 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 

Step 4: Delete unwanted key columns  

Question: I am not sure how to accomplish this from step 3 but

2. 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.. 😞

Please can you help me 1. If my approach is right ? If right or wrong, please can you share best way forward 

2. How to do it? and any reference link would be appreciated.

Thanks in advance

[PijushRoy]

Please share data or sample data

[acerNZ]

@PijushRoy 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 & C) in coloumns
2. Data format should be Asia (DD/MM/YYYY)
3. Please note that each ID has different set of Keys and values
4. This type of data runs in about 180000+ rows with IDs more than 60,000 unique IDs which are repeated with multiple values.

PLease advice

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

[PijushRoy]

Hi acerNZ

Do you want to show your data like below?

power query transpose

want to show data in POWER BI visualization or want to make it in POWER QUERY?

[acerNZ]

Hi @PijushRoy 

That is perfect but couple of things 

1. Date format has to DD/MM/YYYY

2. What is the best practices in Power BI / Data.. to have blanks on empty fields are fill it with null?

Thanks a lot

[PijushRoy]

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.

Find the pbix file - https://drive.google.com/file/d/1cZ4fRc957rS8jQghU7nJMGc3Onm9hqN-/view?usp=sharing

[acerNZ]

Thank you @PijushRoy  I will wait for Date stuff and executed your solution and I got the concept. When re-doing it, I have some issues may be I create seperate thread.