See all rows in tabel visualisation

[Anonymous]

Hi all,

I am trying to combine 2 tables into 1 from 2 different sources.

When I made the relationship and try to use the table visualisation, not all the IDs is being displayed, is there any way I can show all IDs in the table and rows where no data will show the data as blank?

(I simplified it for easier understanding) 

Table 1:   

1

2

3

4

5

3

3

Table 2:

2	$100
3	$85
5	$55
9	$75

The 2 columns that I am trying to form a relationship with is the 2 IDs and I am expecting this output:

1
2	$100
3	$85
3	$85
3	$85
4
5	$55

The current relationship they have now is a many to many relationship for ID. The expected data may not make sense but other columns which exist. I just wanted to simplify it.

Many thanks in advanced 🙂

[Anonymous]

Hi @Greg_Deckler 

My data in ID looks like this.

A12_2KSPF3_A01

SEB/CA/2/38

A12_2KSNPF7_A01

ACB/C10A12-01-09-09/1

Thank you so much for trying to help me with this 

[Greg_Deckler]

@Anonymous - OK, here is my theory. Buried in your list of ID's are "duplicates", something like the following:

A12_2KSPF3_A01

a12_2KSPF3_A01

That's what I am thinking. Something that DISTINCT isn't catching but the relationships see as duplicates. I know this has come up before. Let me see if I can research it.

Another possibility, trailing special characters or spaces. Make sure that in your Power Query that you use both Trim and Clean operations on your columns that eventually become the ID column.

Another option, create your bridge table in Power Query. Reference your original queries and create 2 new queries that only have that column in them. Use an Append query to union them. Then do a Remove duplicates operation.

[Anonymous]

 Hi @Greg_Deckler 

So it is this that is causing my data to be repeated again and again, right?

As my data is currently repeating every single row for every single value of RunAmp. So if I manage to solve this, my data should be fine?

Thank you for your help.

[Greg_Deckler]

@Anonymous - I am very sure that if you solve the bridge table to have truly unique values so that in the relationships they show up as 1-* and not *-* then yes, there is a high probability that most of your headaches go away.

[Anonymous]

Hi @Greg_Deckler 

I tried to create your bridge table in Power Query. Reference your original queries and create 2 new queries that only have that column in them. Use an Append query to union them. Then do a Remove duplicates operation.

And it works! However, I get the errror message below and I cant seem to show the data.

[Greg_Deckler]

@Anonymous - Hooray! Task 1 accomplished, get an appropriate bridge table. Where are you getting that error? When you refresh or ? 

[Anonymous]

@Greg_Deckler 

Haha yeap task1 completed! I got that error when I tried to add RunAmp from the loadlist to my table visualisation according to the Cable Name and it states can't display visual and I got that error message.

[Greg_Deckler]

@Anonymous - OK, that's odd because I can see that the relationship exists between all those tables. Let's do a sanity check. How is your visual configured? You should be using the Bridge table's Cable Number in your visual and not the other Cable Number sitting in your other table.

[Anonymous]

@Greg_Deckler 

Ahh... But I would like to show the data from the Cable Schedule to retrieve the RunAmp value from the load list. 

Either way, I still got the same error message after changing it. I'm not quite sure why.

[Greg_Deckler · Accepted solution]

@Anonymous - Well, it really shouldn't matter which one you choose. Keep the bridge table column and try changing those relationships to single direction versus Both.

[Anonymous]

@Greg_Deckler 

I managed to show the data after changing the RunAmp from "don't summarise" to sum however, the data set is being repeated for every single row which I can't seem to understand why.

[Greg_Deckler]

@Anonymous - Do you need to switch a Don't Summarize on like Description to First or something?

[Anonymous]

@Greg_Deckler 

Apologies, I don't quite understand what you mean.

Did you mean this? Or is there something else I should change?

[Greg_Deckler]

@Anonymous - Yes, that's what I was referring to.

[Anonymous]

@Greg_Deckler 

I tried all of it and it can't seem to work I get the number being printed out repeatedly multiple times.

The odd thing is I reopened my original file when the relationship was many-to-many (despite the bridge built through DAX saying all the values are distinct) which was not ideal and it surprisingly works now. Tried to build the bridge table in the query to get a 1-* relationship again and I get the issue of the item being printed multiple times for every single row.

I am quite confused on what I should do; to fix the relationship and get that right or leave it as many-to-many as long as I get to see the data I want (not sure if I will get issues in the future).

Thanks in advanced for your help

[Greg_Deckler]

@Anonymous - Without getting my hands on the PBIX I'm not sure how to troubleshoot.

[FarhanAhmed]

Any visualization group your duplicate data (any number of column selected) in to 1 and perform aggregation based on values. 

In your case 3 will not repeat 3 times and you will see 3 one time and value as aggregation (either SUM, AVG or No summarization)

For unmatched data you can right click field and select "show items with no data" to get missing values

If you want to view all of your data from table 1, you need to create index column and bring that index column in visualization so that group can become separate and hence all rows will be return from table 1

you can refer this article 

https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

[Greg_Deckler]

@Anonymous - You might have an option, if you click on the drop down in the Visualizations pane of a column to "Show items with no data". That might help. Otherwise, you might do this:

Measure =
  VAR __Calc = <some calculation>
RETURN
  IF(ISBLANK(__Calc) || __Calc="",0,__Calc)

[amitchandak]

@Anonymous ,

One is the max measure on table2 when table 2 is joined with Table 1

Max(Table[value])

Another option is a new column in table 1

MaxX(filter(Table2, Table2[Id] =Table1[Id]), Table2[Value])

[Greg_Deckler]

@Anonymous - The sample data does not say many-to-many to me but will assume that your larger dataset does. Avoid many-to-many if at all possible. Use a bridge table. You can create one like this:

Bridge Table =
  DISTINCT(
    UNION(
      SELECTCOLUMNS('Table 1',"ID",[ID]),
      SELECTCOLUMNS('Table 2',"ID",[ID]),
    )
  )