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 

SundarRaj's avatar
SundarRaj
Super User
1 year ago

Hi Anonymous , 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