Forum Discussion
Helper IIITranspose of table doesn't work.. Says too large data.
Helper IIIPijushRoy 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 |
Community Champion
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