Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Adding new column based on row and column?

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 ...
Need Help
  • slorin's avatar
    slorin
    1 year ago

    Hi Anonymous 

     

    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 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
slorin
Super User
1 year ago

Hi Anonymous 

 

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