Adding new column based on row and column?

[pbinuby]

Hi all,

I'm trying to add a new custom column based on specific values in my table. Data is text file with breaks that gets imported into Power Query as follows:

Trying to format data in Power Query to get the final result as the following:

Any help is appreciated, thanks.

edit: updated screen shots to include better detail

[v-veshwara-msft]

Hi @pbinuby ,

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.

[v-veshwara-msft]

Hi @pbinuby ,
Thank you for engaging with the Fabric Community.

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

@pbinuby , 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.

[wdx223_Daniel]

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

[MarkLaf]

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

Column1	Column2
!Cat1=Testing1
!Year=2025
!Period=JAN
!Company=CompanyAA
!View=YTD
Net Income	24,585.00
Operating Income	2,151.00
COGS	21,548.00
Revenue	21,488.00
Operating Income before Amort	45,846.00
!Cat1=Testing1298
!Year=2025
!Period=FEB
!Company=CompanyBB
!View=Month
Net Income	54,565.00
Operating Income	22.00
COGS	148.00
Revenue	6,984.00
Operating Income before Amort	41,323.00

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:

[SundarRaj]

Hi @pbinuby , 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

[slorin]

Hi @pbinuby 

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 

[pbinuby]

Thank you @slorin ; I think your code is definitely on the right track but I omitted some details for the sake of simplicity. To expand, the "descriptions" are different, they are different line item categories, which are numerous and not just limited to the examples in my screenshots. I've updated my screenshots in the original post. I think your code is on the right track; any help is much appreciated!

[slorin]

Hi @pbinuby 

let
Source = Your_Source,
Columns = List.Transform(List.FirstN(Source[DATA], 5), each Text.BetweenDelimiters(_, "!", "=")),
Group = Table.Group(Source, {"DATA"}, 
   {{"data", (x) => 
     {Table.RemoveFirstN(x, 5)} &
     {#table(Columns, {List.FirstN(x[DATA], 5)})}  }},
   GroupKind.Local,
   (x,y)=>Byte.From(Text.StartsWith(y[DATA],"!Cat"))),
Combine = Table.FromRows(Group[data], {"Table1", "Table2"}),
Expand = Table.ExpandTableColumn(Combine, "Table2", Columns, Columns),
Result = Table.ExpandTableColumn(Expand, "Table1", {"DATA", "Column1"}, {"DATA", "Column1"})
in
Result

Stéphane