Using Power Query to transform multiple columns of question and answer data into a model for power bi

Edited to include the custom function
You can generate the "AnswerHistory" table from your original table with just a single query.

Algorithm should be explained by the notes in the query

let

//read in the initial data table
    Source = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],

//Remove unneeded columns
    #"Cols to Remove" = List.Select(Table.ColumnNames(Source), each _ = "Overall Score" or _ = "ClientID" or Text.EndsWith(_,"Summary")),
    #"Remove Cols" = Table.RemoveColumns(Source,#"Cols to Remove"),

//Add the memberdatekey column
//   This is not needed to develop this particular table
//and set it into the proper position
    #"Add MemberDatekey" = Table.AddColumn(#"Remove Cols", "MemberDatekey", 
        each [MemberID] & DateTime.ToText([DateCompleted],"MM/dd/yyyy" & "#(lf)" & "hh:mm"), type text),
    #"Reordered Columns" = Table.ReorderColumns(#"Add MemberDatekey", {"MemberID", "MemberDatekey"} & List.RemoveFirstN(Table.ColumnNames(#"Remove Cols"),1)),

//Unpivot columns except for the first three
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"MemberID", "MemberDatekey", "DateCompleted"}, "Attribute", "Value"),

//Split column contents so as to create unique values to pivot on
//Note renaming of the split columns in the function from the default
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", 
        Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute", "QuestionID"}),

//Using a custom function, Pivot the "Attribute" column
//  custom function needed because the basic Pivot with no aggregation function will return an error.
//  This can also be done using native functions, if the custom function is too slow
    #"Pivot No Aggregation" = fnPivotNoAggregation(#"Split Column by Character Transition","Attribute","Value"),

//Remove the Question text column as it is no longer needed
    #"Removed Columns" = Table.RemoveColumns(#"Pivot No Aggregation",{"Question"}),

//Set the data types for the columns
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"MemberID", type text}, {"MemberDatekey", type text}, {"DateCompleted", type datetime}, {"QuestionID", Int64.Type}, {"Answer", type any}, {"Score ", Int64.Type}}),

//Replace the null score generated for "age" by 0 as per your spec
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Score "})
in
    #"Replaced Value"

Custom Function
Enter as Blank Query and Rename: fnPivotNoAggregation

//credit: Cam Wallace  https://www.dingbatdata.com/2018/03/08/non-aggregate-pivot-with-multiple-rows-in-powerquery/

//Rename:  fnPivotNoAggregation

(Source as table,
    ColToPivot as text,
    ColForValues as text)=> 

let
     PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))),
     #"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _),
 
    TableFromRecordOfLists = (rec as record, fieldnames as list) =>
    
    let
        PartialRecord = Record.SelectFields(rec,fieldnames),
        RecordToList = Record.ToList(PartialRecord),
        Table = Table.FromColumns(RecordToList,fieldnames)
    in
        Table,
 
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)),
    #"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames),
    #"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames)
in
    #"Expanded Values"

If you desire the clients to come out in a specific order, some sorting steps may need to be added