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 

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
1 year ago

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