Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Conditional tables, database with conditions

  • 1.  Conditional tables, database with conditions

    Posted 15 days ago
    Hello
    I have 5 coulms (column1, column2, column3, column 4, column5 )
    with words like:dog, home, cat, mouse, bike,car, window

    and i d like to create
    five additionals columns (column1f, column2f, column3f, column 4f, column5f) with but when there will be some special words like 'mouse or bike' i d like to put there 0.

    So i have got somethink like that:



    but in future there will be more 'special words' and i d like to avoid updating conditions in this 5 columns, is it possible to create some 'database' with words : mouse, bike,  and if i ll put another word there it will automatically add another condition to my column?



    ------------------------------
    Artur dasd
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Conditional tables, database with conditions

    Top Contributor
    Posted 14 days ago
      |   view attached
    Hi @Artur dasd

    Please see if the following helps you.

    1. Create a table using the enter data method (this can also be a list coming in from excel / csv etc) for the excluded words. Whenever required, you can add new words or remove existing words from this list.
    2. Then, add the custom columns with the following M code:
    Table.AddColumn(#"Added Custom2", "Column4f", each if List.Contains(Table.ToList(ExcludedValues),[Column4]) = true then 0 else [Column4], type text)​

    PFA the pbix file for your reference.



    ------------------------------
    Gopa Kumar
    Limner Consulting

    North Kerala User Group Leader
    https://www.pbiusergroup.com/NorthKerala
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Conditional tables, database with conditions

    Posted 13 days ago
    Hello
    Thanks for your help.
    It works fine, but I d like to change one thing.
    So i have got Excluded Values, with mouse and bike inside..

    When there is a text in row like mouse it shows 0 (its fine)
    but when there is a text like mouse123 it shows mouse123,

    how can i modify that expression to not check if there is a exact value, but to check if it starts with mouse?

    Thanks for your help!
    I have tried with 
    each if List.Contains​ANY
    but i couldnt make it work

    ------------------------------
    Artur dasd
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Conditional tables, database with conditions

    Top Contributor
    Posted 13 days ago
      |   view attached
    Hi @Artur dasd

    Please see if the following helps you. I added a function to check for the partial match. I have not checked the performance against large data. 


    Mcode for the function

    (temptext as text) =>  //variable passed for the value to be checked (column 1, column 2 etc)
      let
        Source = ExcludedValues, //the exclusion values table
        //-----------------------------------------------------------
        //create a function check each value in the exclusion list and see if there is a match with the variable (column 1 value etc)
        fnloop = (exclude as text) => 
          let
            output = if Text.Contains(temptext, exclude) then 0 else 1 //check if the variable passed CONTAINS the exclusion value
          in
            output,
        //-----------------------------------------------------------
        outputtable1 = Table.AddColumn(Source, "CheckValue", each fnloop([Value])), //adding a column to the exclusion table run the function to check the text match and return 0 if there is a partial match and 1 if no match
        outputtable2 = Table.SelectRows(outputtable1, each [CheckValue] = 0), //filter only the row which is 0 i.e matched
        finaloutput = if Table.IsEmpty(outputtable2) then temptext else 0 //if table is empty, it means there is no match and hence return the variable value(column 1 value etc) and 0 otherwise
      in
        finaloutput

    PFA the pbix for your reference.



    ------------------------------
    Gopa Kumar
    Limner Consulting

    North Kerala User Group Leader
    https://www.pbiusergroup.com/NorthKerala
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: Conditional tables, database with conditions

    Posted 12 days ago
    Hey
    its working
    But,, as you said.. speed is not awesome with large data that i have :D
    But anyway many thanks for you!

    ------------------------------
    Artur dasd
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: Conditional tables, database with conditions

    Top Contributor
    Posted 12 days ago
      |   view attached
    Hi @Artur dasd

    Can you check with the below Mcode for the function and see if the performance is better or worse.
    (temptext as text) => 
      let
        Source = Table.ToList(ExcludedValues),
        TotalLoops = List.Count(Source),
        //---------------------------------------
        fnloop = (loop as number) => 
          let
            currentloop = loop + 1, //loop counter
            currentlistIndex = currentloop - 1, //the index of the keyword check list starts from 0 and not 1 hence we reduce 1 from the currentloop value which starts from 1
            currentcheckvalue = Source{currentlistIndex},
            textcheck = Text.Contains(temptext, currentcheckvalue), //check to see if the year value is contained in the remarks
            output = 
              if textcheck = true then 
                "0"
              else if currentloop >= TotalLoops then 
                temptext
              else 
                @fnloop(currentloop)
          in
            output,
        //---------------------------------------
        finaloutput = fnloop(0)
      in
        finaloutput​

    PFA the pbix file for your reference.

    ------------------------------
    Gopa Kumar
    Limner Consulting

    North Kerala User Group Leader
    https://www.pbiusergroup.com/NorthKerala
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 7.  RE: Conditional tables, database with conditions

    Posted 11 days ago
    Hey
    I have no idea why second function doenst want to work..
    Take a look: FirstOne is wokring


    But the second one shows error... I have no idea why :<


    ------------------------------
    Artur dasd
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 8.  RE: Conditional tables, database with conditions

    Top Contributor
    Posted 11 days ago
    Hi @Artur dasd

    I am not able to reproduce the issue at my end. ​Can you delete the 2 instances of "finaloutput" and retype them manually and check? If the issue still persist, is it possible to share a sample pbix with the errror reproduced?

    ------------------------------
    Gopa Kumar
    Limner Consulting

    North Kerala User Group Leader
    https://www.pbiusergroup.com/NorthKerala
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 9.  RE: Conditional tables, database with conditions

    Posted 10 days ago
    retyping finaloutput helps ;-)

    So i have got comparation how long reflesh data takes, with my data inside.
    Option 1 -from my 1st post ->8 sec
      #"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "Column1f", each if [Column1] = "mouse" then 0 else if [Column1] = "bike" then 0 else [Column1]),
        #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Column2f", each if [Column2] = "bike" then 0 else if [Column2] = "mouse" then 0 else [Column2]),
        #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Column3f", each if [Column3] = "bike" then 0 else if [Column3] = "mouse" then 0 else [Column3]),
        #"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "column4f", each if [Column4] = "bike" then 0 else if [Column4] = "mouse" then 0 else [Column4]),
        #"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "column5f", each if [Column5] = "bike" then 0 else if [Column5] = "mouse" then 0 else [Column5]),
    

    Option 2- 1min 44sek
    (temptext as text) =>  //variable passed for the value to be checked (column 1, column 2 etc)
      let
        Source = ExcludedValues, //the exclusion values table
        //-----------------------------------------------------------
        //create a function check each value in the exclusion list and see if there is a match with the variable (column 1 value etc)
        fnloop = (exclude as text) => 
          let
            output = if Text.Contains(temptext, exclude) then 0 else 1 //check if the variable passed CONTAINS the exclusion value
          in
            output,
        //-----------------------------------------------------------
        outputtable1 = Table.AddColumn(Source, "CheckValue", each fnloop([Value])), //adding a column to the exclusion table run the function to check the text match and return 0 if there is a partial match and 1 if no match
        outputtable2 = Table.SelectRows(outputtable1, each [CheckValue] = 0), //filter only the row which is 0 i.e matched
        finaloutput = if Table.IsEmpty(outputtable2) then temptext else 0 //if table is empty, it means there is no match and hence return the variable value(column 1 value etc) and 0 otherwise
      in
        finaloutput​
    Option 3 - 1min:10sek
    (temptext as text) => 
      let
        Source = Table.ToList(ExcludedValues),
        TotalLoops = List.Count(Source),
        //---------------------------------------
        fnloop = (loop as number) => 
          let
            currentloop = loop + 1, //loop counter
            currentlistIndex = currentloop - 1, //the index of the keyword check list starts from 0 and not 1 hence we reduce 1 from the currentloop value which starts from 1
            currentcheckvalue = Source{currentlistIndex},
            textcheck = Text.Contains(temptext, currentcheckvalue), //check to see if the year value is contained in the remarks
            output = 
              if textcheck = true then 
                "0"
              else if currentloop >= TotalLoops then 
                temptext
              else 
                @fnloop(currentloop)
          in
            output,
        //---------------------------------------
        finaloutput = fnloop(0)
      in
        finaloutput​​


    I  d like to thank you for all help. but is there any way to somehow change my 1st code:
    to something like:

    #"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "Column1f", each if [Column1] = ExcludedValues[0] then 0 else if [Column1] = = ExcludedValues[1] then 0 else [Column1]),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Column2f", each if [Column2] = ExcludedValues[0] then 0 else if [Column2] = ExcludedValues[1] then 0 else [Column2]),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Column3f", each if [Column3] = ExcludedValues[0] then 0 else if [Column3] = ExcludedValues[1] then 0 else [Column3]),

    I dont know Mcode well, so maybe you will be able to help ;)



    ------------------------------
    Artur dasd
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 10.  RE: Conditional tables, database with conditions

    Top Contributor
    Posted 10 days ago
    Hi @Artur dasd

    If you are ok with changing the code for each column one by one whenever a new exclusion word is added, try the following code :

    if Text.Contains([Column4], ExcludedValues[Value]{0}) then 0 else if Text.Contains([Column4], ExcludedValues[Value]{1}) then 0 else [Column4]


    ------------------------------
    Gopa Kumar
    Limner Consulting

    North Kerala User Group Leader
    https://www.pbiusergroup.com/NorthKerala
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 11.  RE: Conditional tables, database with conditions

    Posted 9 days ago

    Hey

    Its working. Its exactly what i need.
    Thank you again for all your support!



    ------------------------------
    Artur dasd
    ------------------------------

    Academy - Online Interactive Learning from Experts