Creating a new table from a master table

[CHealy]

I am looking to create a table from another table. The master table has a column with several questions for a specific ID and a second column with the reponse. The response is in several data types ( text, dates and numbers). The questions are repeated for each distinct ID but not repeated multiple times for an ID. So Q1 is asked only once for aaa and bbb. There are also to many questions to convert this from long to wide. 

I am looking to create a second table that has the distinct ID and the response to select questions as the rows and the columns being the ID, and the questions I I need the responses to. 

I have tried several methods and failed each time. The most I can do is create a table with the ID and one of the questions and I would like to avoid having to make multiple tables like this. 

Any help would be appriciated. 

[Anonymous]

Hi @CHealy 

Please follow these steps:

1.        split column

2.         return desktop，create a matrix.

3.        Off column subtotals , row subtotals. 
   
4.        Final output

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

^{Best Regards,}

^{Yifan Wang}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[CHealy]

Sorry this is not what I am trying to do. Let me see if I can explain a little more. 

I have columns for ID, questions, reponses, and several columns of additional data. I have way to many questions to make a table where I pivot the data from long to wide. 

I would like to make a table where I have one row for each ID. A column for the first questions I want to look at and the data in that column be the response for that question. Then another column for a second question with its corresponding data And so on for a couple of questions but NOT all the questions. I also do not need this to be a table on the display but a data table that I can use as a reference link to other data tables. (something that I can see in the table view and manipulate, along with use in the model view to make relationships)

Currently I am able to make an addtional table for one question and the corresponding response, but I cannot add in addtional columns for the other questions I am looking at. I would like to avoid making multiple tables for each question I would need to look at. 

ID	Q1- date	Q2- name	Q3- location	Q6- something else
First ID	Date for 1	Name for 1	Location for 1	Response for 1
Second ID	Date for 2	Name for 2	Location for 2	Response for 2