Replace Blanks with 0 in Table Visual with multiple data sources

[aafflick]

Hi, I have a large Table visual with fields from 4 different data source tables. I want to show a 0 in the places where there are blanks now. I have seen other solutions but none that are related to having multiple data source tables. 

Here is a sample of the data fields I am using and Table visual: 

Table 1

Parent Account	Child Account	Start Date	State
Alliance	Central Rehab	1/1/2020	NY
Invigorate	Trace Health	2/1/2018	FL
Preferred	Grace Point H & R	3/8/2011	CA

Table 2

Parent Account	Child Account	June Rate	July Rate
Alliance	Central Rehab	20	53
Invigorate	Trace Rehab	3	67

Table 3

Parent Account	Child Account	June Score	July Score
Invigorate	Trace Rehab	100	98
Preferred	Grace Point H & R	25	75

Table 4

Parent Account	Child Account	June Patient Count	July Patient Count
Alliance	Central Rehab	254	189
Invigorate	Trace Rehab		25
Preferred	Grace Point H & R	80	101

[v-stephen-msft · Accepted solution]

Hi @aafflick ,

Situation 1: Your June Rate, July Rate, June Sorce, etc. are all measures.
You can refer to this post.

1.Create a new table named Table(5) with the child account column and the parent account column. The two columns of data must contain all of the four tables.

2.Create relationships between the four tables and the new table, and then the parent account column and child account column in the visual are selected from the new table. Make sure add +0 to the calculation result of measures.In this way, the blanks on the visual will be replaced with 0.

Situation 2: Your June Rate, July Rate, June Sorce, etc. are all values from the data source.

1.Select Merge Queries as New.

2.Both select the Parent Account column and click OK.

3.Expand the columns you need and don't select "Use original column name as prefix".

4.Table and Table(2) are merged.

5. Repeat the operation just now to merge the Merge1 table with Table (3) and Table (4), and get the following.

6.Use Replace Values to replace null with 0.(Remember to select the column where the null is located before performing the replacement operation.)

7.Now the blanks in the view are replaced with 0

You can check more details from here.


Best Regards,
Stephen Tao

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

[Anonymous]

Are you using measures or calculated columns for the rate/count or is that data from the source? If a measure, make sure it's data type is a number and include a +0 the end of the calculation.

Otherwise I would consider adding a Replace Value step in Power Query that replaces the blank values with a 0. 

[aafflick]

Table Visual Results

Parent Account	Child Account	Start Date	State	June Rate	July Rate	June Score	July Score	June Patient Count	July Patient Count
Alliance	Central Rehab	1/1/2020	NY	20	53			254	189
Invigorate	Trace Health	2/1/2018	FL	3	67	100	98		25
Preferred	Grace Point H &	3/8/2011	CA			25	75	80	101

Desired Table Visual Results

Parent Account	Child Account	Start Date	State	June Rate	July Rate	June Score	July Score	June Patient Count	July Patient Count
Alliance	Central Rehab	1/1/2020	NY	20	53	0	0	254	189
Invigorate	Trace Health	2/1/2018	FL	3	67	100	98	0	25
Preferred	Grace Point H &	3/8/2011	CA	0	0	25	75	80	101