none
Table.ReplaceValue RRS feed

  • Question

  • Hi : I want to populate empty strings or null value based on the text string which exist in the closest left hand side column, using Table.ReplaceValue function. As per below, OU_Lvl_4 can be populated from OU_lvl_3 and OU_lvl_5 can be populated from OU_lvl_4 and so on.

    The second scenario underneath that Ou_lvl_5 to be populated from OU_lvl_4 (which is already populated) and so on. Would I be able to make it work dynamically?

    Any assistance would be greatly appreciated.

    Input:

    Output:

    Thursday, July 18, 2019 1:36 AM

Answers

  • Hi,

    Previous code is by scratch, so you don't need first line. Also in start topic null values were supposed, so the code is not handles empty values (rather, multiple spaces, as per your file). You may add such query in your file:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        cols = Table.ColumnNames(Source),
        trim = Table.TransformColumns(Source,List.Transform(cols, each {_, Text.Trim})),
        f = (x) => not List.Contains({"",null}, x),
        replace = Table.ReplaceValue(trim,0,each if f([OU_Lvl_5]) then [OU_Lvl_5]
                                       else if f([OU_Lvl_4]) then [OU_Lvl_4]
                                       else if f([OU_Lvl_3]) then [OU_Lvl_3]
                                       else if f([OU_Lvl_2]) then [OU_Lvl_2]
                                       else [OU_Lvl_1],
                                       (a,b,c)=>if f(a) then a else c, List.Skip(cols))
    in
        replace

    Note, the code works correctly, if there are no filled values to the right of empty value (i.e., empty value, null, multiple spaces). Imagine, OU_Lvl_5 column will be filled with some value. Then all empty values will be filled with this value (not only OU_Lvl_6 column, but OU_Lvl_3, OU_Lvl_4 columns too). If it's the case you may split replace step into several steps (for each column, starting from the last).

    • Marked as answer by M.Awal Sunday, July 21, 2019 8:57 AM
    • Edited by Aleksei Zhigulin Sunday, July 21, 2019 9:03 AM
    Saturday, July 20, 2019 10:24 PM

All replies

  • Hi,

    Specifically in this case, following approach should work for limited amounts of data:

    let
        Source = YourTable,
        transpose = Table.Transpose(Source),
        fill = Table.FillDown(transpose,Table.ColumnNames(transpose)),
        transpose1 = Table.Transpose(fill, Table.ColumnNames(Source))
    in
        transpose1
    Thursday, July 18, 2019 7:41 AM
  • Hi Aleksei: Thanks for providing a solution for this. There are other columns on the right in the real data set and the data set is quite large. I tried to use Table.ReplaceValue function in OU_Lvl_3 column saying if it is empty replace with null through the UI. After the code gets generated I then take the null of replace with each _ [OU_Lvl_3]. However, its not populating any value from col OU_lvl_3

    Thursday, July 18, 2019 10:17 PM
  • Hi, 

    Then you may use this code:

    = Table.ReplaceValue(YourPreviousStep,0,each if [OU_Lvl_4] = null then [OU_Lvl_3] else [OU_Lvl_4],(a,b,c)=>c,{"OU_Lvl_4", "OU_Lvl_5","OU_Lvl_6"})


    Friday, July 19, 2019 10:06 AM
  • Hi, 

    Then you may use this code:

    = Table.ReplaceValue(YourPreviousStep,0,each if [OU_Lvl_4] = null then [OU_Lvl_3] else [OU_Lvl_4],(a,b,c)=>c,{"OU_Lvl_4", "OU_Lvl_5","OU_Lvl_6"})


    Would this work if there exists a text in OU_Lvl_5 and OU_Lvl_6 needs to get the value from it? 

    Friday, July 19, 2019 10:40 AM
  • No, it wouldn't. The code solve specific problem on pictures in start topic. More robust solution (but not comprehensive):

    let
        Source = #table(4, List.Zip({{1..5},{null, null, 8..10}, {null, null, 3, null, null}, List.Repeat({null},5)})),
        replace = Table.ReplaceValue(Source,0,each if [Column3] <> null then [Column3]
                                              else if [Column2] <> null then [Column2]
                                              else [Column1],
                                              (a,b,c)=>if a = null then c else a,{"Column2", "Column3", "Column4"})
    in
        replace

    Friday, July 19, 2019 12:01 PM
  • Hi Aleksei: Thanks for your prompt response.  I am not sure how I am going to apply the first line of your code in my  data set. Source = #table(4, List.Zip({{1..5},{null, null, 8..10}, {null, null, 3, null, null}, List.Repeat({null},5)})),

    I provided a link below with the sample data. There are 6 levels in total in the data set. Because the data is coming from a text file when I pull the data in Query Editor the blank cell do not show as null its just shows empty.

    I have also tried with the previous solution. The data is not getting populated in the empty cells.


    https://1drv.ms/x/s!Amc8fiGpDxekhgCvBhufTe1SCmIg

    Saturday, July 20, 2019 4:24 AM
  • Hi,

    Previous code is by scratch, so you don't need first line. Also in start topic null values were supposed, so the code is not handles empty values (rather, multiple spaces, as per your file). You may add such query in your file:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        cols = Table.ColumnNames(Source),
        trim = Table.TransformColumns(Source,List.Transform(cols, each {_, Text.Trim})),
        f = (x) => not List.Contains({"",null}, x),
        replace = Table.ReplaceValue(trim,0,each if f([OU_Lvl_5]) then [OU_Lvl_5]
                                       else if f([OU_Lvl_4]) then [OU_Lvl_4]
                                       else if f([OU_Lvl_3]) then [OU_Lvl_3]
                                       else if f([OU_Lvl_2]) then [OU_Lvl_2]
                                       else [OU_Lvl_1],
                                       (a,b,c)=>if f(a) then a else c, List.Skip(cols))
    in
        replace

    Note, the code works correctly, if there are no filled values to the right of empty value (i.e., empty value, null, multiple spaces). Imagine, OU_Lvl_5 column will be filled with some value. Then all empty values will be filled with this value (not only OU_Lvl_6 column, but OU_Lvl_3, OU_Lvl_4 columns too). If it's the case you may split replace step into several steps (for each column, starting from the last).

    • Marked as answer by M.Awal Sunday, July 21, 2019 8:57 AM
    • Edited by Aleksei Zhigulin Sunday, July 21, 2019 9:03 AM
    Saturday, July 20, 2019 10:24 PM
  • I am very impressed with your M coding style.  Greatly appreciated. Thanks again.

    Sunday, July 21, 2019 8:59 AM
  • You're welcome)
    Sunday, July 21, 2019 9:06 AM