Creating custom column to count rows from 2nd table in power query

Hi devaraj 

 

The code below doesn't use merge

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMjI0BZFKsTowEXNTCzQRMxOEiImpGZBtagwiDZFETIws4SLmFiC2uZkJkDRQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, #"reference id" = _t, #"custom column for count of valid value id" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"reference id", Int64.Type}, {"custom column for count of valid value id", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Reference IDs", each let 
PrevStep = #"Changed Type",
CurrentID = [id],
FilteredRows = Table.SelectRows(PrevStep, each [id] = CurrentID)[reference id]
in FilteredRows),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Count", each let 
t2 = table2, //actual table2 name
filteredT2 = Table.SelectRows(t2, each [state] = "valid"),
refID = filteredT2[value id],
count =List.Count(List.Intersect({[Reference IDs],refID}))
in 
count),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Reference IDs"})
in
    #"Removed Columns"

 

As I mentioned in my initial response, whether you perform a merge or not, this process will be very slow and could even result in a timeout if applied to millions of records. You’ll need to either handle this in DAX (though it may still be slow) or perform the transformation at the source, rather than within Power BI.