Forum Discussion

Deb3107's avatar
Deb3107
Frequent Visitor
7 years ago

Transpose columns from 1 table and create a new table

Hi,

I have t able with 30+ columns in and I want to create another table with 3 columns from the original table then transpose 8 other columns into 1 column + a value column.

 

The 8 columns I want to transpose all have a value of true or false. 

 

 I tried creating a summary table first using the following

 

Unable to refer = SUMMARIZECOLUMNS('Growth Hub Referrals'[_new_growthhub_value],'Growth Hub Referrals'[new_growthhubreferralid],'Growth Hub Referrals'[Date],'Growth Hub Referrals'[alreadyreferred],'Growth Hub Referrals'[businessclosed],'Growth Hub Referrals'[businessincrisis],'Growth Hub Referrals'[camefromGH],'Growth Hub Referrals'[customerdeclined],'Growth Hub Referrals'[not eligible],'Growth Hub Referrals'[notinterested],'Growth Hub Referrals'[signposted])
 
But then I can't access it using Power Query. I don't want to change the existing table which is why I created the  unable to refer table.
 
Any ideas? Is this even possible?
 
Debra
Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Possible to upload some sample/dummy data with the expected output?

    • Deb3107's avatar
      Deb3107
      Frequent Visitor
      7 years ago

      Hi,

      this is some sample data

      new_growthhubreferralid_new_growthhub_valuecreatedonunable_alreadyreferredunable_businessclosedunable_businessincrisisunable_camefromGHunable_customerdeclinedunable_not eligibleunable_notinterestedunable_signpostednew_ghrbusinessagenew_ghrbusinessagemeetshubrequirementsnew_ghrbusinessclosednew_ghrbusinessplanningnew_ghrdirectlyrequestedcontactwithgrowthhubnew_ghrcapitalequipmentpurchasenew_ghrgrowthpotentialnew_ghrlookingtocompletestafftrainingnew_ghrlookingtoexportnew_ghrlookingtorecruitstaffnew_ghrmeetsectorspecificrequirements_new_case_value
      6141476d-bd39-e911-a8be-00224801a32d4aec0216-f3fe-e811-a8db-00224801ac1226/02/2019 11:55FALSEFALSEFALSEFALSEFALSETRUEFALSEFALSETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEc9801ac1-3439-e911-a8d9-00224801a6b7
      1ba970cc-d439-e911-a8e6-00224801a4ba54ec0216-f3fe-e811-a8db-00224801ac1226/02/2019 14:43FALSEFALSEFALSEFALSETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE01bf2adb-c439-e911-a8d9-00224801a6b7
      86702954-7b3a-e911-a8e6-00224801a4ba54ec0216-f3fe-e811-a8db-00224801ac1227/02/2019 10:35FALSEFALSEFALSEFALSEFALSEFALSETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE06df1c7b-e139-e911-a8e7-00224801ae66

       

      and this is how i need it to transpose to

       

      _new_growthhub_valuecreatedonNot referred ReasonCount
      4aec0216-f3fe-e811-a8db-00224801ac1226/02/2019 11:55unable_alreadyreferred2
      54ec0216-f3fe-e811-a8db-00224801ac1226/02/2019 14:43unable_businessclosed3
      54ec0216-f3fe-e811-a8db-00224801ac1227/02/2019 10:35unable_businessincrisis5
      68ec0216-f3fe-e811-a8db-00224801ac1227/02/2019 13:44unable_camefromGH7
      8aec0216-f3fe-e811-a8db-00224801ac1227/02/2019 14:04unable_customerdeclined9
      64ec0216-f3fe-e811-a8db-00224801ac1227/02/2019 14:47unable_not eligible10
      8eec0216-f3fe-e811-a8db-00224801ac1227/02/2019 16:36unable_notinterested11
      7aec0216-f3fe-e811-a8db-00224801ac1227/02/2019 16:57unable_signposted12
      80ec0216-f3fe-e811-a8db-00224801ac1227/02/2019 17:23unable_alreadyreferred2
      6aec0216-f3fe-e811-a8db-00224801ac1227/02/2019 17:27unable_businessclosed3
      7aec0216-f3fe-e811-a8db-00224801ac1227/02/2019 17:35unable_businessincrisis5
      7aec0216-f3fe-e811-a8db-00224801ac1227/02/2019 17:51unable_camefromGH7
      7aec0216-f3fe-e811-a8db-00224801ac1227/02/2019 17:52unable_customerdeclined9
      68ec0216-f3fe-e811-a8db-00224801ac1228/02/2019 09:14unable_not eligible10
      64ec0216-f3fe-e811-a8db-00224801ac1228/02/2019 09:47unable_notinterested11
      7aec0216-f3fe-e811-a8db-00224801ac1228/02/2019 10:28unable_signposted12

       

      Thanks

       

      Debra