Forum Discussion
Filter value from column with multiple values
If you know in advance about all such cases, you could handle it as follows...
Flavour = VAR CheckOV = SEARCH("OV",FlavourData[Groupcode],1,BLANK()) VAR Check2 = SEARCH("2",FlavourData[Groupcode],1,BLANK()) VAR CheckX = SEARCH("X",FlavourData[Groupcode],1,BLANK()) VAR CheckOH = SEARCH("OH",FlavourData[Groupcode],1,BLANK()) VAR CheckH = SEARCH("H",FlavourData[Groupcode],1,BLANK()) VAR Result = SWITCH( TRUE(), NOT(ISBLANK(CheckOV)),"Contains OV", NOT(ISBLANK(Check2)),"Contains 2", NOT(ISBLANK(CheckX)),"Contains X", NOT(ISBLANK(CheckH)) && ISBLANK(CheckOH),"Contains H", NOT(ISBLANK(CheckOH)),"Contains OH", "Wrong Value" ) RETURN ResultIf you don't know about all such cases, then you have to figure out some other way.
It's always does contain or not contain. The results I need are the differrent "flavours" I know beforehand, so it has to be more than 2 of them.
Using nested if condition or SWITCH function, we can populate as many values as we want in a field. But in your cases, the GroupCode has more than one flavour in it. Therefore, you are forced to give only two outputs.
The automatically generated table option seems to not have this problem. Could you please explain me how to create this filtered table? You've helped me so much already, thanks!
I can help you with the power query. For that, please post the autogenerated powerquery of your existing table. In case you don't know, it is available at Transform->Advanced Editor.
A SWITCH function could possibly work I guess. My "flavours" will always be a single value. It cannot happen that there will be more then 1 "flavour" in the groupcode.
That said, the power query of my existing table with the groupcodes in them is:
let
Bron = Excel.Workbook(File.Contents("C:\Users\michael.rensen\OneDrive - SDR\Bureaublad\PowerBi Hans\SegmentenOpdrachtgever rapport Hans.xlsx"), null, true),
Atrium_Sheet = Bron{[Item="Atrium",Kind="Sheet"]}[Data],
#"Headers met verhoogd niveau" = Table.PromoteHeaders(Atrium_Sheet, [PromoteAllScalars=true]),
#"Type gewijzigd" = Table.TransformColumnTypes(#"Headers met verhoogd niveau",{{"Relatiegroep", type text}, {"Aanmaakdatum", type datetime}, {"Opbrengst", type number}, {"Kostenbedrag", type number}, {"Winst/verlies % (F)", type number}, {"Hoofdproject code", type text}, {"Aantal uur gecalculeerd", type number}, {"Winstbedrag (totaal)", type number}, {"Geboekte uren", type number}, {"Opdrachtgever korte naam", type text}, {"Kosten gecalculeerde uren", type number}, {"Kosten uren werkelijk", type number}, {"Opdrachtgever code", Int64.Type}, {"Hoofdproject titel", type text}}),
#"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Type gewijzigd", "Segment", each null),
#"Namen van kolommen gewijzigd" = Table.RenameColumns(#"Aangepaste kolom toegevoegd",{{"Hoofdproject code", "Project Nr."}})
in
#"Namen van kolommen gewijzigd"
The column 'Relatiegroep' is the one with all the different flavours with the "noise" in them that needs to be filtered. I have another table with all the "flavours" listed.
Basically I would need something like this, but the following code is obviously not working as intended. Is there a way to rewrite this to make it work?
From "Get Data" choose the new "Blank Query" and paste the following code. Do not modify your existing table's powerquery.
let Bron = Excel.Workbook(File.Contents("C:\Users\michael.rensen\OneDrive - SDR\Bureaublad\PowerBi Hans\SegmentenOpdrachtgever rapport Hans.xlsx"), null, true), Atrium_Sheet = Bron{[Item="Atrium",Kind="Sheet"]}[Data], #"Headers met verhoogd niveau" = Table.PromoteHeaders(Atrium_Sheet, [PromoteAllScalars=true]), #"Type gewijzigd" = Table.TransformColumnTypes(#"Headers met verhoogd niveau",{{"Relatiegroep", type text}, {"Aanmaakdatum", type datetime}, {"Opbrengst", type number}, {"Kostenbedrag", type number}, {"Winst/verlies % (F)", type number}, {"Hoofdproject code", type text}, {"Aantal uur gecalculeerd", type number}, {"Winstbedrag (totaal)", type number}, {"Geboekte uren", type number}, {"Opdrachtgever korte naam", type text}, {"Kosten gecalculeerde uren", type number}, {"Kosten uren werkelijk", type number}, {"Opdrachtgever code", Int64.Type}, {"Hoofdproject titel", type text}}), #"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Type gewijzigd", "Segment", each null), #"Namen van kolommen gewijzigd" = Table.RenameColumns(#"Aangepaste kolom toegevoegd",{{"Hoofdproject code", "Project Nr."}}), ListOfGroupCodesList = List.Distinct(Table.Column(#"Namen van kolommen gewijzigd","Relatiegroep")), ListOfGroupCodesTable = Table.RenameColumns(Table.FromList(ListOfGroupCodesList),{{"Column1","Relatiegroep"}}), AddCodes = Table.AddColumn( ListOfGroupCodesTable, "Flavour", each Text.Split(Record.Field(_,"Relatiegroep"),";") ), ExpandFlavour = Table.RenameColumns( Table.RemoveColumns( Table.AddColumn( Table.ExpandListColumn(AddCodes, "Flavour"),"Flavour2",each Text.Trim(Record.Field(_,"Flavour")) ), {"Flavour"} ) ,{{"Flavour2","Flavour"}} ) in ExpandFlavourHopefully, it will give you a new table with all the individual values. You can then create a relationship between your flavours to this table and another relationship from this table to the data table. Not sure, but based on your data model you decide.
Thanks for the reply! I used the power query to add the new table. And indeed, it has al the differt combinations in the rows. But when I make the relation between the tables and want to filter the results i
-Get the good rows, but also some other non relevant rows-Rows that are duplicated in the view (x amount times that the group code has different values).
Is this an easy fix? Or am I doing something completely wrong?
Is it perhaps a better solution to rewrite this into something that works with a SWITCH statement? Thanks!Flavour = VAR CheckOV = SEARCH("OV",FlavourData[Groupcode],1,BLANK()) VAR Check2 = SEARCH("2",FlavourData[Groupcode],1,BLANK()) VAR CheckX = SEARCH("X",FlavourData[Groupcode],1,BLANK()) VAR Result = SWITCH( TRUE(), NOT(ISBLANK(CheckOV)),"Contains OV", NOT(ISBLANK(Check2)),"Contains 2", NOT(ISBLANK(CheckX)),"Contains X", "Wrong Value" ) RETURN Result