Question and Answer Field Values needed in One Row.

[JCortez]

I currently have two field in a table called Question and Answer. The data is currently appearing like this:


I would like for it to appear like this:


Any assistance would be greatly appreciated, thank you in advance.

[v-hjannapu]

Hi @JCortez,

I would also take a moment to thank @Ashish_Mathur , @Royel for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

Best Regards,
Harshitha.

[v-hjannapu]

Hi @JCortez,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.

Regards,
Harshitha.

[Ashish_Mathur]

Hi,

In the output table, do you want to create one row for each unique combination of MRN and Date?

[JCortez]

Yes

[Ashish_Mathur]

This will have to be done in Power Query.  All the columns should be or should be brought in 1 table.

[JCortez]

I am unsure of how to do this, the fields are coming from several tables. This is where they are coming from (sql):

'Client'[med_rec_no]
'ASSESS_QUES'[question]
'Client_Assess_Response'[response]
'Client_Assessment'[clas_name]
'Client_Assessment'[clas_date]
'Client_Assessment'[clas_signed]

Thank you for your assistance.

[Royel]

Hi @JCortez  Thanks for your questions. 

Lets use this M-Code in power query and solve the problem 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjM0tlDSUXJMyc0sLs7Mz1NwSSxJBQqY6hsZ6xsZGJkA2fEBjsEKJiZooiFBoa5KsTpwIwKK8tOLEnMV/Epzk1KLwAKZKakKGqbGBmaaRJuC4RBDI31jA0yXAIVJcYqzP9AhppqEjIgFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MRN = _t, Question = _t, Answer = _t, Assessment = _t, Date = _t, Signed = _t]),
    
    // Group by MRN, Assessment, Date, Signed
    #"Grouped Rows" = Table.Group(Source, {"MRN", "Assessment", "Date", "Signed"}, {
        {"AllData", each _, type table}
    }),
    
    // Create dynamic Question-Answer columns based on actual count
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "QA_Record", each 
        let
            Data = [AllData],
            RowCount = Table.RowCount(Data),
            
            // Create record with only the required number of Question/Answer pairs
            QA_Record = List.Accumulate(
                {0..RowCount-1},
                [],
                (state, index) => 
                    let
                        CurrentRow = Data{index},
                        QuestionColumn = "Question" & Text.From(index + 1),
                        AnswerColumn = "Answer" & Text.From(index + 1),
                        NewFields = Record.FromList(
                            {CurrentRow[Question], CurrentRow[Answer]},
                            {QuestionColumn, AnswerColumn}
                        )
                    in
                        Record.Combine({state, NewFields})
            )
        in
            QA_Record
    ),
    
    // Get all unique column names across all records
    AllQAColumns = List.Distinct(
        List.Combine(
            List.Transform(
                #"Added Custom"[QA_Record], 
                each if _ <> null then Record.FieldNames(_) else {}
            )
        )
    ),
    
    // Expand the QA_Record into separate columns
    #"Expanded QA_Record" = Table.ExpandRecordColumn(#"Added Custom", "QA_Record", AllQAColumns, AllQAColumns),
    
    // Remove the AllData column
    #"Removed Columns" = Table.RemoveColumns(#"Expanded QA_Record", {"AllData"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Question1", type text}, {"Answer1", type date}, {"Question2", type text}, {"Answer2", type text}})
in
    #"Changed Type"

Output: 

If you get lost try this sample file: https://limewire.com/d/MTIYY#7XdXxAeAea 

Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!

[JCortez]

Will this work if the fields are coming from several tables? This is where they are coming from:

'Client'[med_rec_no]
'ASSESS_QUES'[question]
'Client_Assess_Response'[response]
'Client_Assessment'[clas_name]
'Client_Assessment'[clas_date]
'Client_Assessment'[clas_signed]

Thank you for your assistance.

[v-hjannapu · Accepted solution]

Hi @JCortez,

Thank you  for reaching out to the Microsoft fabric community forum.

use this M-Code in power query and solve the problem 

let
    Source = Table.FromRows({
        {"6138", "Admission Date", "5/23/2024", "_PAS 44", "5/23/2024", "TRUE"},
        {"6138", "Program Number", "Pride (53069)", "_PAS 44", "5/23/2024", "TRUE"},
        {"6138", "Admission Date", "12/30/2024", "_PAS 44", "12/23/2024", "TRUE"},
        {"6138", "Program Number", "PCO (55)", "_PAS 44", "12/23/2024", "TRUE"}
    }, {"MRN", "Question", "Answer", "Assessment", "Date", "Signed"}),

   
    #"Changed Types" = Table.TransformColumnTypes(Source, {
        {"MRN", Int64.Type},
        {"Question", type text},
        {"Answer", type text},           
        {"Assessment", type text},
        {"Date", type text},             
        {"Signed", type logical}
    }),

   
    #"Grouped Rows" = Table.Group(#"Changed Types", {"MRN", "Assessment", "Date", "Signed"}, {
        {"AllData", each _, type table}
    }),

  
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "QA_Record", each 
        let
            Data = [AllData],
            RowCount = Table.RowCount(Data),
            QA_Record = List.Accumulate(
                {0..RowCount-1},
                [],
                (state, index) => 
                    let
                        CurrentRow = Data{index},
                        QuestionCol = "Question" & Text.From(index + 1),
                        AnswerCol = "Answer" & Text.From(index + 1),
                        NewFields = Record.FromList(
                            {CurrentRow[Question], CurrentRow[Answer]},
                            {QuestionCol, AnswerCol}
                        )
                    in
                        Record.Combine({state, NewFields})
            )
        in
            QA_Record
    ),

   
    AllQAColumns = List.Distinct(
        List.Combine(
            List.Transform(
                #"Added Custom"[QA_Record], 
                each if _ <> null then Record.FieldNames(_) else {}
            )
        )
    ),

   
    #"Expanded QA_Record" = Table.ExpandRecordColumn(#"Added Custom", "QA_Record", AllQAColumns, AllQAColumns),

    
    #"Removed Columns" = Table.RemoveColumns(#"Expanded QA_Record", {"AllData"})
in
    #"Removed Columns"

I tested it with sample data, and it worked fine. Please find the attached screenshot and Pbix for your reference.

Hope this helps if you have any queries we are  happy to assist you further.
Best Regards,
Harshitha.

[v-hjannapu]

Hi @JCortez,

I wanted to follow up and see if you have had a chance to review the information that was shared. If you have any additional questions or need further clarification, please don’t hesitate to reach out. I am here to assist with any concerns you might have.

Regards,
Harshitha.