Question and Answer Field Values needed in One Row.

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!

Hi,

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

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.