Adding new column based on row and column?

I think this should work. There are some different approaches that might be faster, so let me know if performance is bad with your text source query.

I hard-entered your provided values into Power Query for testing purposes. Note for future, please provide your data in a format like this to make it easier to quickly copy into Power BI.

TextQuery

Below is the M to transform this into your desired output. It's written as a separate query. This only works if TextQuery table has consistent structure throughout (five rows of query argument-like vals, then five rows of FieldName, FieldValue (in currency) pairings).

//Approach: manually construct the rows by parsing the text input, 
//then construct the output table from rows
let
    Source = TextQuery,

    //Since this is coming in as text, we should first convert Col2 to currency
    Col2Type = Table.TransformColumnTypes(Source,{{"Column2", Currency.Type}}),
    
    //Convert to rows (list of rows where each row is list of vals)
    ToRows = Table.ToRows( Col2Type ),

    //Split by grouping of rows that we need to transform together
    Split = List.Split( ToRows, 10 ),

    //This is where we convert each group of 10 rows from Source into 5 rows with desired output vals
    ConstructRows = 
    List.Transform( 
        Split, 
        each let 
            //DATA and Column1 already in format we need, skip the repeating query arg rows to grab
            DataVals = List.Skip( _, 5 ), 
            //Get repeating query arg vals (what will be: Cat1,Year,...)
            QueryVals = { _{0}{0}, _{1}{0}, _{2}{0}, _{3}{0}, Text.Middle( _{4}{0}, 6 ) } 
        in 
            //Add repeating QueryVals to each row that already has DataVals
            List.Transform( DataVals, each _ & QueryVals ) 
    ),
    
    //Convert list of lists of newly constructed rows to flat list of rows (again, a row is a list of vals)
    CombineRows = List.Combine( ConstructRows ),

    //Construct table, this is where we speciy column names and types of our output
    ToTable = 
    Table.FromRows( 
        CombineRows, 
        type table [ DATA=text, Column1=Currency.Type, Cat1=text, Year=text, Period=text, Company=text, View=text ] 
    )

in
    ToTable

Output: