filter the final output based on column_ mquery

[Anonymous]

hello, 

i have below condition to implemnt in power query, 

if source column contain source 1, final output should only have source 1,else source 2. cant be both.

id	var1	var2	var3	Source
1	10	12	15	source 1
2	14	14	14	source 2
3	11	15	11	source 2
4	11	18	15	source 1
5	11	10	12	source 1
6	20	20	15	source 2
7	13	12	12	source 2
8	16	15	16	source 1
9	11	12	13	source 1
10	12	19	13	source 1

[AlB · Accepted solution]

Hi @Anonymous 

Paste this M code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0ABFGIMIUSBTnlxYlpyoYKsXqRCuBRU1QCKi8EVjeGCRqCNMKZqHIm8DlLbCaD9eFcASKvBlQwMgARiDrh5hvDhI1hrvfCF0ebKsZ3H1m6OZbwu03gpmEIo8UNpaYCmIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, var1 = _t, var2 = _t, var3 = _t, Source = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"var1", Int64.Type}, {"var2", Int64.Type}, {"var3", Int64.Type}, {"Source", type text}}),
    hasSource1 = List.Contains(#"Changed Type"[Source], "source 1"),
    res = Table.SelectRows(#"Changed Type", each if hasSource1 then [Source]="source 1"  else [Source]="source 2")
in
    res

 Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers