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

[devaraj]

Hello, 

I am looking of a powerquery to create a custom column to acheive below column from two different tables, without using dax formulas or merging the tables 

table 1
id	refrence id	custom column for count of valid value id
123	215	2
123	758	2
123	648	2
456	536	1
456	429	1
789	764	0

table 2
value id	state
215	valid
758	valid
648	invalid
536	invalid
429	valid
764	invalid

Custom column for count of valid value is the columns I want to acheive using power query without merging table 2 to table 1, as I have millions of records and its quite perfomamce issue when merging the tables and sont want to use dax formulas too. Is there a possiblity to get this column using powerquery ?

thanks

Devaraj

[v-sathmakuri]

Hi @devaraj ,

I hope this information provided is helpful. Feel free to reach out if you have any further questions or would like to discuss this in more detail. If responses provided answers your question, please accept it as a solution so other community members with similar problems can find a solution faster.

Thank you!!

[v-sathmakuri]

Hi @devaraj ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you!!

[v-sathmakuri]

Hi @devaraj ,

Thank you for reaching out to Microsoft Fabric Community.

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[danextian]

Hi @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.

[devaraj]

hi @danextian the count values are coreesponding to id column, id 123 has two valid id state,

[danextian · Accepted solution]

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.

[Ashish_Mathur]

Hi,

How did you generate those numbers - 2,2,2,1,1,0?

[devaraj]

hi @Ashish_Mathur the numbers 2,2,2,1,1,0 are  the count values are coreesponding to id column, id 123 has two valid id state,