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 

slorin's avatar
slorin
Super User
1 year ago

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 

  • 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