filter the final output based on column_ mquery

Question

hello,&nbsp;&nbsp;i have below condition to implemnt in power query,&nbsp;&nbsp;if source column contain source 1, final output should only have source 1,else source 2. cant be both.idvar1var2var3Source1101215source 12141414source 23111511source 24111815source 15111012source 16202015source 27131212source 28161516source 19111213source 110121913source 1&nbsp;

alb · Accepted Answer

Hi Anonymous&nbsp;
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
&nbsp;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&nbsp;
&nbsp;

Forum Discussion

filter the final output based on column_ mquery

1 Reply

Related content

VAR and SWITCH Output issues

DMV Column Output Definitions and Options

Filter

DateDiff Incorrect output

Data Variances between dataflow output and Lakehouse data

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