Forum Discussion

acerNZ's avatar
acerNZ
Icon for Helper III rankHelper III
5 years ago

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

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 ...
Need Help
Show and Tell
Tutorial Requests
PijushRoy's avatar
PijushRoy
Icon for Community Champion rankCommunity Champion
5 years ago

Please share data or sample data

  • acerNZ's avatar
    acerNZ
    Icon for Helper III rankHelper III
    5 years ago

    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 numberData KeyData Value
    1Hire date23/05/2010
    1GenderM
    1DOB 
    1Phone12121232338
    1Phone2123122243
    1ApprovedYes
    1CostUSD 2300
    2Hire dateMay 1 2018
    2GenderF
    2DOB04/22/1977
    2Phone11234567890
    2Phone24987654321
    2ApprovedYes
    2CostUSD 1400
    2PositionStaff mgr
    2LocationBoulder
    2reports toGeorge Naunce
    2Employee typeContract
    3Hire date4th Mar 2016
    3DeptQuality
    3CostUSD 1500
    4Hire dateMar 6th 2018
    4GenderMale
    4DOB08/26/1979
    4Phone1-
    4Phone2-
    4ApprovedNo
    4CostUSD 5000
    4PositionSales head
    4LocationDenver
    4reports to 
    4Employee typeFull Time
    4Vehicle allowanceYes
    • PijushRoy's avatar
      PijushRoy
      Icon for Community Champion rankCommunity Champion
      5 years ago

      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's avatar
        acerNZ
        Icon for Helper III rankHelper III
        5 years ago

        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