Adding new column based on row and column?

Hi Anonymous 

let
Source = Your_Source,
Group = Table.Group(Source, {"DATA"}, 
   {{"data", (x) => Table.FromRows(
      {{Table.SelectRows(x, each [DATA]="Description")} & List.FirstN(x[DATA], 4)},
      {"Description", "Cat1", "Year", "Period", "Company"}) }},
   GroupKind.Local,
   (x,y)=>Byte.From(Text.StartsWith(y[DATA],"!Cat"))),
Combine = Table.Combine(Group[data]),
Expand = Table.ExpandTableColumn(Combine, "Description", {"DATA", "Column1"}, {"DATA", "Column1"})
in
Expand

or

let
Source = Your_Source,
Group = Table.Group(Source, {"DATA"}, 
   {{"data", (x) => Table.FromRows(
       {{Table.SelectRows(x, each [DATA]="Description")} &
          List.Transform(List.FirstN(x[DATA], 4), each Text.AfterDelimiter(_,"="))},
       {"Description", "Cat1", "Year", "Period", "Company"}) }},
   GroupKind.Local,
   (x,y)=>Byte.From(Text.StartsWith(y[DATA],"!Cat"))),
Combine = Table.Combine(Group[data]),
Expand = Table.ExpandTableColumn(Combine, "Description", {"DATA", "Column1"}, {"DATA", "Column1"})
in
Expand

Stéphane 

Hi Anonymous , here's a solution that you can look at. Thanks!

The Code:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"!Cat1=Testing1", type text}, {"Column1", Int64.Type}}),
Items = Table.SelectRows(#"Changed Type", each [Column1] <> null),
List = List.RemoveItems(#"Changed Type"[#"!Cat1=Testing1"],List.Distinct(Items[#"!Cat1=Testing1"])),
#"Filtered Rows" = List.Count(Table.SelectRows(#"Changed Type", each Text.Contains([#"!Cat1=Testing1"], "Period"))[#"!Cat1=Testing1"]),
Table = Table.FromRows(List.Repeat(List.Split(List,List.Count(List) / #"Filtered Rows"),List.Count(List) / #"Filtered Rows")),
Sorted = Table.Sort(Table, each Text.End([Column1],1)),
Custom1 = Table.TransformColumns(Table.AddIndexColumn(Sorted,"Cols",0,1),{"Cols", each Items{_}}),
#"Expanded Cols" = Table.ExpandRecordColumn(Custom1, "Cols", {"!Cat1=Testing1", "Column1"}, {"!Cat1=Testing1", "Column1.1"}),
Renamed = Table.RenameColumns(#"Expanded Cols",{{"Column1.1", "Amount"}, {"Column5", "View"}, {"Column3", "Period"}, {"Column4", "Company"}, {"Column2", "Year"}, {"Column1", "Cat1"}}),
Reorder = Table.ReorderColumns(Renamed,{"!Cat1=Testing1", "Amount", "Cat1", "Year", "Period", "Company", "View"})
in
Reorder

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:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom1 = Table.FromRecords(List.Accumulate(Table.ToRecords(Source),{{},[]},(x,y)=>if Text.StartsWith(y[DATA],"!") then {x{0},x{1}&Expression.Evaluate("["&Text.Replace(Text.Middle(y[DATA],1),"=","=""")&"""]")} else {x{0}&{y&x{1}},x{1}}){0})
in
    Custom1

Hi Anonymous ,
Thank you for engaging with the Fabric Community.

And thanks to everyone who contributed. Your input is truly valued.

Anonymous , were you able to resolve your query? If so, kindly mark the helpful response as "Accepted Solution" to assist others who may have the similar question. If a different solution worked, please share it here and mark it as "Accepted Solution" to benefit the community.

If you still need help, feel free to let us know.

Thank you.

Hi Anonymous ,

Just checking in again as we haven’t heard back from you. If the provided solutions addressed your issue, kindly mark the helpful reply as the Accepted Solution to assist others with similar queries.

If your issue is still unresolved or you need further help, feel free to reach out.
Thank you.

Hi Anonymous ,

We’re following up once more regarding your query. If it has been resolved, please mark the helpful reply as the Accepted Solution to assist others facing similar challenges.

If you still need assistance, please let us know.
Thank you.