none
Conditional select value by split string and merge query RRS feed

  • Question

  • Hi team,

    I have a problem as following:

    In the first table there are two accounts value A & B and I need to find the price value based on the condition

    For account A the price information I need to split the condition value and find the price data.

    For account B, I need to do a lookup or merge query with table B to find the price data.

    My current way to do it is to split table A into 2 tables which separate account A and B to apply separate way to get the data I need.

    Is that possible to add a column in power query do apply both methods to get the data I need?

    such as

    if [Account]="A" then  "some code"

    else

    "some code"


    • Edited by Tom J Sun Thursday, January 5, 2017 1:09 AM upload picture
    Thursday, January 5, 2017 1:08 AM

Answers

  • Hi Tom,

    my suggestion would be this:

    let
        Source = Excel.CurrentWorkbook(){[Name="TableA"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", type text}, {"Condition", type text}}),
        #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Condition"},TableB,{"DATA"},"NewColumn",JoinKind.LeftOuter),
        #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Price"}, {"Price"}),
        #"Added Custom" = Table.AddColumn(#"Expanded NewColumn", "PriceNew", each if [Price]=null then Text.Split([Condition], " "){0} else [Price])
    in
        #"Added Custom"

    :-)


    Imke Feldmann TheBIccountant.com

    • Marked as answer by Tom J Sun Thursday, January 5, 2017 10:12 PM
    Thursday, January 5, 2017 12:53 PM
    Moderator

All replies

  • Hi Tom,

    my suggestion would be this:

    let
        Source = Excel.CurrentWorkbook(){[Name="TableA"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", type text}, {"Condition", type text}}),
        #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Condition"},TableB,{"DATA"},"NewColumn",JoinKind.LeftOuter),
        #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Price"}, {"Price"}),
        #"Added Custom" = Table.AddColumn(#"Expanded NewColumn", "PriceNew", each if [Price]=null then Text.Split([Condition], " "){0} else [Price])
    in
        #"Added Custom"

    :-)


    Imke Feldmann TheBIccountant.com

    • Marked as answer by Tom J Sun Thursday, January 5, 2017 10:12 PM
    Thursday, January 5, 2017 12:53 PM
    Moderator
  • Hi Imke,

    Got ur point.

    That works.

    Cheers,

    Tom Sun

    Thursday, January 5, 2017 10:12 PM