Forum Discussion

MaryamSe's avatar
MaryamSe
Frequent Visitor
2 years ago
Solved

Connecting a Refrence table

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. 

 

 

 

 

 

 

 

 

 

 

 

 

 

  • 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-Forum/ba-p/963216

    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/1447523/highlight/true#M607150

    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's avatar
    Anonymous
    2 years ago

    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.

     

4 Replies

  • foodd's avatar
    foodd
    Community Champion

    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-Forum/ba-p/963216

    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/1447523/highlight/true#M607150

    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's avatar
    Anonymous
    Not applicable

    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.