Forum Discussion
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.
Super UserHi devaraj
It is unclear how you came up with the expected result column - reference id 215 has one valid row in table2 but your result is 2. Also, whether you merge or not, this is going to be slow if you have millions of records. This transformation is best done at the source. Alternatively, I would use DAX for this but still expect for some hiccups.
- danextian
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.
