combine csv files with inconsistent columns and missing columns

After looking a little more at the output of the simple CSV combination above, I think I better understand your original request.

I think what you are aiming for is to pivot all your field/value column pairs, where the field is in the href column and value is in the corresponding non-href column. E.g. [b343055e href] is the "total_rev_trend_score" field (removing extra finbox path text) and [b343055e] is a percentage value like "85.7%"

A normal pivot won't work since you have many field/value columns stacked horizontally.

Here is M code building off above that does this full transformation.

let
    Source = FolderConnection,

    // Combine all csvs as is
    ParseCsvBinary = Table.TransformRows(Source, each Table.PromoteHeaders(Csv.Document([Content]))),
    CombineCsvTables = Table.Buffer( Table.Combine(ParseCsvBinary) ),

    // Perform pivot on every field,value column pair
    FixRows = Table.TransformRows( 
        CombineCsvTables, 
        each [
            all = _, 
            first3rec = Record.SelectFields( all, List.FirstN( Record.FieldNames(all), 3 ) ), 
            fieldvals = Record.ToList( 
                Record.SelectFields( all, List.RemoveFirstN( Record.FieldNames(all), 3 ) ) 
            ), 
            fields = List.Transform( 
                List.Alternate( fieldvals, 1, 1, 1 ), 
                each Text.AfterDelimiter(_,"/",{0,Occurrence.Last}) 
            ), 
            vals = List.ReplaceMatchingItems( 
                List.Alternate( fieldvals, 1, 1 ), 
                {{"-",null},{"",null}}
            ) , 
            fixedfieldvalrec = Record.FromTable( 
                Table.FromColumns( {fields,vals}, type table [Name=text,Value=text] ) 
            ), 
            finalrow = first3rec & fixedfieldvalrec 
        ][finalrow] ),
    DynRowType = [
        firstrec = List.First( FixRows ), 
        fields = Record.FieldNames( firstrec ), 
        fieldcount = Record.FieldCount(firstrec), 
        typerecs = List.Repeat( { [Type=type nullable text,Optional=false] }, fieldcount ), 
        rectypeval = Record.FromList(typerecs,fields), 
        rectype = Type.ForRecord(rectypeval,false) 
    ][rectype],
    RowsToTable = Table.FromRecords( FixRows, type table DynRowType ),
    
    // Transform all columns to proper type
    SimpleTypeConvert = Table.TransformColumnTypes(
        RowsToTable, {
            {"total_rev_trend_score", Percentage.Type}, {"eps_trend_score", Percentage.Type}, 
            {"fcf_levered_trend_score", Percentage.Type}, {"gp_trend_score", Percentage.Type}, 
            {"fin_health_growth_score", type number}, {"fin_health_profit_score", type number}, 
            {"fin_health_cash_flow_score", type number}, {"total_rev_cagr_3y", Percentage.Type}, 
            {"total_rev_cagr_5y", Percentage.Type}, {"total_rev_growth", Percentage.Type}, 
            {"fcf_to_ni", Percentage.Type}, {"fcf_levered_share", Currency.Type}, 
            {"fcf_levered_growth", Percentage.Type}, {"fcf_levered_cagr_3y", Percentage.Type}, 
            {"fcf_levered_cagr_5y", Percentage.Type}, {"fcf_yield_ltm", Percentage.Type}, 
            {"fcf_yield_avg_5y", Percentage.Type}, {"asset_price_return_1y", Percentage.Type}, 
            {"first_trade_date", type date}
        }
    ),
    funcNumberTBMK = Value.ReplaceType( 
        (x as nullable text) as nullable number => 
        if x = null then null 
        else [ 
            parts = Text.Split( x, " " ), 
            numpart = Number.From(parts{0}), 
            tailpart = List.Skip( parts ), 
            tailpartfix = List.First( 
                List.ReplaceMatchingItems( tailpart, {{"T",1e12},{"B",1e9},{"M",1e6},{"K",1e3}} ) 
            ), 
            final = numpart * (tailpartfix ?? 1 )
        ][final], 
        type function (x as text) as nullable Currency.Type 
    ),
    funcNumberX = 
        (x as nullable text) as nullable number => 
        if x = null then null else Number.FromText( Text.Remove(x, "x") ),
    funcOutputType = (x as function) as type => Type.FunctionReturn( Value.Type(x) ),
    ComplexTypeConvert = Table.TransformColumns(
        SimpleTypeConvert, 
        List.Transform(
            {"marketcap","total_rev","fcf_levered","ni_company"}, 
            each {_,funcNumberTBMK, funcOutputType(funcNumberTBMK) }
        ) & List.Transform(
            {"price_to_book"}, 
            each {_,funcNumberX, funcOutputType(funcNumberX) }
        ) 
    )

in
    ComplexTypeConvert

Output: