Forum Discussion
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 ...
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
ResultStéphane
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.