Connecting a Refrence table

[MaryamSe]

Hi, I have created a PIN refrence that includes an ID, year and a custome coulumn (=Number.ToText([PIN])& Number.ToText([Program Year]). ) 

The PIN refrence table is created by appending a few other tables: 

// Load the first table and select the column to append
Source1 = Table.SelectColumns(T1, {"PIN","Program Year"}),
Distinct1 = Table.Distinct(Source1),

// Load the second table and select the column to append
Source2 = Table.SelectColumns(T2, {"PIN","Program Year"}),
Distinct2 = Table.Distinct(Source2),

......

// Combine the distinct values from all tables
FinalTable = Table.Combine({Distinct1, Distinct2,Distinct3,Distinct4,Distinct5,Distinct6}),
// Get distinct values from the final table
FinalDistinctTable = Table.Distinct(FinalTable),
#"Removed Duplicates" = Table.Distinct(FinalDistinctTable),
#"Added Custom" = Table.AddColumn(#"Removed Duplicates", "PIN_Year", each Number.ToText([PIN])& Number.ToText([Program Year])),
#"Removed Duplicates1" = Table.Distinct(#"Added Custom")
in
#"Removed Duplicates1"

I have also a Fram Type reference table that includes all the different farm types. I created a table and just listed the different type of the farms. 

I connceted the Farm Type table to all other tables ( one to many -single).  I created a slicer from it there is no Blank box in the slicer and works fine. However, when I conncet the PIN refrence table to the other tables ( one to one- both; it does not give me any other option) the Blank box appreas in Farm Type slicer. it is something with the relastionships, but I cannot figure it out. Could you please help with this. 

 

[foodd · Accepted solution]

Hello @MaryamSe , and thank you for sharing a question with the Community.   This reply is informational.   Please remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.  Proud to be a Super User!

[Anonymous · Accepted solution]

Hi @MaryamSe ,

For one-to-many relationships, table expansion takes place from the "many" to the "one" sides by using semantics. When a matching value from the "many" to the "one" side doesn't exist, a blank virtual row is added to the "one" side table.

You can refer to this learn more:Model relationships in Power BI Desktop - Power BI | Microsoft Learn

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[MaryamSe]

Thank you! Fixed

[foodd · Accepted solution]

Hello @MaryamSe , and thank you for sharing a question with the Community.   This reply is informational.   Please remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.  Proud to be a Super User!

[MaryamSe]

Thanks! I could fix it finally.