Forum Discussion

vedran's avatar
vedran
Frequent Visitor
8 years ago
Solved

New row based on previous row, ideas?

Hi there, I would like just ask for ideas.. have a transaction details table, and all details are in proper format expect 2 columns (Option and OptionAmount), goes like this:   TransDate | Customer...
Need Help
Tips and Tricks
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    Something like this:

     

    let
    Source = TransactionDetails,
    #"Added Custom" = Table.AddColumn(Source, "ProductOptions", each Text.Split([Product]&","&[Option],","), type {text}),
    #"Expanded ProductList" = Table.ExpandListColumn(#"Added Custom", "ProductOptions"),
    #"Added Index" = Table.AddIndexColumn(#"Expanded ProductList", "Index", 0, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index",{"ProductOptions"},PriceList,{"Option"},"PriceList",JoinKind.LeftOuter),
    #"Expanded PriceList" = Table.ExpandTableColumn(#"Merged Queries", "PriceList", {"Price"}, {"PriceList.Price"}),
    #"Sorted Rows" = Table.Sort(#"Expanded PriceList",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "NewAmount", each if [Product] = [ProductOptions] then [Amount] else [PriceList.Price], type number),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Product", "Amount", "Option", "OptionAmount", "Sum", "PriceList.Price"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"ProductOptions", "Product"}, {"NewAmount", "Amount"}})
in
    #"Renamed Columns"
MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

Something like this:

 

let
    Source = TransactionDetails,
    #"Added Custom" = Table.AddColumn(Source, "ProductOptions", each Text.Split([Product]&","&[Option],","), type {text}),
    #"Expanded ProductList" = Table.ExpandListColumn(#"Added Custom", "ProductOptions"),
    #"Added Index" = Table.AddIndexColumn(#"Expanded ProductList", "Index", 0, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index",{"ProductOptions"},PriceList,{"Option"},"PriceList",JoinKind.LeftOuter),
    #"Expanded PriceList" = Table.ExpandTableColumn(#"Merged Queries", "PriceList", {"Price"}, {"PriceList.Price"}),
    #"Sorted Rows" = Table.Sort(#"Expanded PriceList",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "NewAmount", each if [Product] = [ProductOptions] then [Amount] else [PriceList.Price], type number),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Product", "Amount", "Option", "OptionAmount", "Sum", "PriceList.Price"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"ProductOptions", "Product"}, {"NewAmount", "Amount"}})
in
    #"Renamed Columns"
  • vedran's avatar
    vedran
    Frequent Visitor
    8 years ago

    Marcel, I dont have better words except, thank you a lot!

    This example goes in a perfect direction, I tried to recreate this example based on real data set, and works perfect.

     

    Please I'd like to at least buy a beer or a lunch :)

     

    Admins please add all Kudos to Marcel, and also this post I will to be marked as a Solution!