locked
Power Query conditional extract middle text from cell RRS feed

  • Question

  • Hi All,

    I have a raw table where more than 50 columns exist. but for my query I extracted only relative columns.

    Issue : I want to fill update Final Adjusted Full Name & Total quantity columns only for cells where Name of offsetting account mentioned as Other ext serv, B or Other ext serv, C and rest values should be remain same.

    And Name and Qty will be picked from column "Name".

    Note : for updating total qty column if we found negative value in Value in Obj. Crcy then we need to convert total qty column to negative as well. 

    Can any body suggest me how to write a custom M-code.

    Below is the sample table & sample Output table.

    Raw Table

    Name Name of offsetting account Total quantity Value in Obj. Crcy Final Adjusted Full Name
    00205Markari,   Ahmulk H:10.00 Other ext serv, B 0 -1000  
    00205Noordeen,   Mohamed H:24.00 Other ext serv, B 0 2000  
    00205Noordeen, Mohamed H:-40.00 Other ext serv, C 0 -1000  
    00205Pervala,   Phani Kumar H:11.00 XXXXX   XXXXX 0 3000  
    00205Pervala,   Phani Kumar H:40.00 Location 34 566  
    01079Barreto,   Guillherme H:16.00 Airfare 0 -600  
    01079Barreto,   Guillherme H:-27.00 Location2 35 345  
    01079Barreto,   Guillherme H:9.00 Other ext serv, B 0 500  
    01079Chava,   Venkataraman Krishna H:24.00 Other ext serv, C 0 1200  
    Classic   - Travel  Jan Airfare 234 346  
    Ram   Singh Airfare2 3 7677 Singh,   Ram
    Ashok   Kumar Location2 0 4355 Kumar,   Ashok

    Sample Output

    Name Name of offsetting account Total quantity Value in Obj. Crcy Final Adjusted Full Name
    00205Markari, Ahmulk H:10.00 Other ext serv, B -10 -1000 Markari, Ahmulk
    00205Noordeen, Mohamed H:24.00 Other ext serv, B 24 2000 Noordeen, Mohamed
    00205Noordeen, Mohamed H:-40.00 Other ext serv, C -40 -1000 Noordeen, Mohamed
    00205Pervala,   Phani Kumar H:11.00 XXXXX   XXXXX 0 3000  
    00205Pervala,   Phani Kumar H:40.00 Location 34 566  
    01079Barreto,   Guillherme H:16.00 Airfare 0 -600  
    01079Barreto,   Guillherme H:-27.00 Location2 35 345  
    01079Barreto, Guillherme H:9.00 Other ext serv, B 9 500 Barreto, lokama
    01079Chava, Venkataraman Krishna   H:24.00 Other ext serv, C 24 1200 Chava, Venkataraman Krishna
    Classic   - Travel  Jan Airfare 234 346  
    Ram   Singh Airfare2 3 7677 Singh,   Ram
    Ashok   Kumar Location2 0 4355 Kumar,   Ashok

    Thanks in advance for valuable feedback.

    Also I want to know if we can see the syntax of list of function in power query itself like normal excel function ?


    Rajender

    Wednesday, March 6, 2019 9:19 PM

Answers

  • Hi Rajender,

    Sorry - I missed that part of the requirement. It's better to add an additional step than to further complicate and existing one, so the final code should be:

    let
        source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        changedType = Table.TransformColumnTypes(
            source,
            {{"Name", type text}, 
             {"Name of offsetting account", 
             type text}, {"Total quantity", Int64.Type}, 
             {"Value in Obj. Crcy", Int64.Type}, 
             {"Final Adjusted Full Name", type text}}
        ),
        replacedTotalQtyValues = Table.ReplaceValue(
            changedType, 
            each 0, 
            each if [Name of offsetting account] = "Other ext serv, B" or 
                    [Name of offsetting account] = "Other ext serv, C" then
                    Number.FromText(Text.AfterDelimiter([Name], ":"))
                  else [Total quantity],
            Replacer.ReplaceValue,
            {"Total quantity"}
       ),
       replacedTotalQtyValues2 = Table.ReplaceValue(
            replacedTotalQtyValues, 
            each [Total quantity], 
            each if [Value in Obj. Crcy] < 0 then
                    -Number.Abs([Total quantity])
                  else [Total quantity],
            Replacer.ReplaceValue,
            {"Total quantity"}
       ),
       replacedFinalAdjustedNameValues = Table.ReplaceValue(
            replacedTotalQtyValues2, 
            each null, 
            each if [Name of offsetting account] = "Other ext serv, B" or 
                    [Name of offsetting account] = "Other ext serv, C" then
                    Text.Middle(Text.BeforeDelimiter([Name], ":"), 5)
                  else [Final Adjusted Full Name],
            Replacer.ReplaceValue,
            {"Final Adjusted Full Name"}
        )
    in
        replacedFinalAdjustedNameValues

    Monday, March 11, 2019 10:58 PM

All replies

  • You can try the following:

    let
        source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        changedType = Table.TransformColumnTypes(
            source,
            {{"Name", type text}, 
             {"Name of offsetting account", 
             type text}, {"Total quantity", Int64.Type}, 
             {"Value in Obj. Crcy", Int64.Type}, 
             {"Final Adjusted Full Name", type text}}
        ),
        replacedTotalQtyValues = Table.ReplaceValue(
            changedType, 
            each 0, 
            each if [Name of offsetting account] = "Other ext serv, B" or 
                    [Name of offsetting account] = "Other ext serv, C" then
                    Number.FromText(Text.AfterDelimiter([Name], ":"))
                  else [Total quantity],
            Replacer.ReplaceValue,
            {"Total quantity"}
       ),
       replacedFinalAdjustedNameValues = Table.ReplaceValue(
            replacedTotalQtyValues, 
            each null, 
            each if [Name of offsetting account] = "Other ext serv, B" or 
                    [Name of offsetting account] = "Other ext serv, C" then
                    Splitter.SplitTextByCharacterTransition(
                        {"0".."9"}, 
                        (char) => not List.Contains({"0".."9"}, char)
                    )(Text.BeforeDelimiter([Name], ":")){1}
                  else [Final Adjusted Full Name],
            Replacer.ReplaceValue,
            {"Final Adjusted Full Name"}
        )
    in
        replacedFinalAdjustedNameValues

    Also I want to know if we can see the syntax of list of function in power query itself like normal excel function ?

    Don't know what you mean.

    Thursday, March 7, 2019 2:54 AM
  • Hi Colin,

    Thanks a lot for your support. I am getting error below error in last step. Please assist.

    Expression.Error: The name 'Splitter.SplitTextByCharacterTransition' wasn't recognized.  Make sure it's spelled correctly.

    Regarding Syntax : Like in excel we are applying any formula then its syntax. For example : if we apply vlookup in excel then a syntax pop up like Lookup_value, table_array,col_index_num, [range_lookup] ?


    Rajender

    Thursday, March 7, 2019 8:04 AM
  • Hi Rajender,

    Appears that you don't have a version of Excel that supports the Splitter.SplitTextByCharacterTransition in Power Query.

    However, since it appears that number preceding the name is fixed in all cases, we can use a simpler text function.

    Try replacing the replacedFinalAdjustedNameValues step with the following:

    replacedFinalAdjustedNameValues = Table.ReplaceValue(
            replacedTotalQtyValues, 
            each null, 
            each if [Name of offsetting account] = "Other ext serv, B" or 
                    [Name of offsetting account] = "Other ext serv, C" then
                    Text.Middle(Text.BeforeDelimiter([Name], ":"), 5)
                  else [Final Adjusted Full Name],
            Replacer.ReplaceValue,
            {"Final Adjusted Full Name"}
        )

    As for your second question, the feature is available in Power BI Desktop, but not in Excel at this time.


    Thursday, March 7, 2019 1:47 PM
  • Hi Colin,

    I am able to get the output as required except if the if "Value in Obj. Crcy" is negative than "Total quantity" should also be in negative. Please suggest in which code line I have to modify further ?

    Regards,

    Rajender


    Rajender

    Monday, March 11, 2019 9:09 PM
  • Hi Rajender,

    Sorry - I missed that part of the requirement. It's better to add an additional step than to further complicate and existing one, so the final code should be:

    let
        source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        changedType = Table.TransformColumnTypes(
            source,
            {{"Name", type text}, 
             {"Name of offsetting account", 
             type text}, {"Total quantity", Int64.Type}, 
             {"Value in Obj. Crcy", Int64.Type}, 
             {"Final Adjusted Full Name", type text}}
        ),
        replacedTotalQtyValues = Table.ReplaceValue(
            changedType, 
            each 0, 
            each if [Name of offsetting account] = "Other ext serv, B" or 
                    [Name of offsetting account] = "Other ext serv, C" then
                    Number.FromText(Text.AfterDelimiter([Name], ":"))
                  else [Total quantity],
            Replacer.ReplaceValue,
            {"Total quantity"}
       ),
       replacedTotalQtyValues2 = Table.ReplaceValue(
            replacedTotalQtyValues, 
            each [Total quantity], 
            each if [Value in Obj. Crcy] < 0 then
                    -Number.Abs([Total quantity])
                  else [Total quantity],
            Replacer.ReplaceValue,
            {"Total quantity"}
       ),
       replacedFinalAdjustedNameValues = Table.ReplaceValue(
            replacedTotalQtyValues2, 
            each null, 
            each if [Name of offsetting account] = "Other ext serv, B" or 
                    [Name of offsetting account] = "Other ext serv, C" then
                    Text.Middle(Text.BeforeDelimiter([Name], ":"), 5)
                  else [Final Adjusted Full Name],
            Replacer.ReplaceValue,
            {"Final Adjusted Full Name"}
        )
    in
        replacedFinalAdjustedNameValues

    Monday, March 11, 2019 10:58 PM
  • Hi Colin,

    Thanks a lot for your valuable support. I checked and mapped it in between existing Power Query steps and output showing correctly.

    Regards,

    Rajender


    Rajender

    Tuesday, March 12, 2019 3:12 PM