Create a Table that includes all data.

[BrunoNM]

Hi All,

I need to create a table where it brings the information from table 1 and table 2 in a single table and I don't know the best way to do it.

Store Name	City	Opening Date
Store 1	São Paulo	01/01/2015
Store 2	Rio de Janeiro	05/05/2016
Store 3	Bahia	06/06/2017
Store 4	Porto Alegre	08/08/2018
Store 5	Brasília	09/09/2019
Store 6	Manaus	10/09/2019
Store 7	Santa Catarina	11/09/2019
Store 8	Fortaleza	12/09/2019
Store 9	Belo Horizonte	13/09/2019
Store 10	Mato Grosso	14/09/2019

Table 1, I have the name of the stores and information.

Store Name	Date Inventory	Nº Invetory	Balance
Store 1	01/10/2022	1054321	3
Store 3	01/10/2022	1112345	2
Store 5	01/10/2022	1275324	2
Store 7	01/10/2022	5139875	5
Store 10	01/10/2022	8754219	9

Table 2 has if the store did the inventory count and the day, if the store did not count it, it does not appear in this table that is generated daily.

These tables are related by the Store Name column

I need to create this table in power bi where it brings the total list of stores and information where there is a count and the store that did not count, it brings it blank or informing that it did not count.

Store Name	Date Inventory	Nº Invetory	Balance
Store 1	01/10/2022	1054321	3
Store 2
Store 3	01/10/2022	1112345	2
Store 4
Store 5	01/10/2022	1275324	2
Store 6
Store 7	01/10/2022	5139875	5
Store 8
Store 9
Store 10	01/10/2022	8754219	9

Thanks

[serpiva64]

Hi,

please post the data and not the image. It's easier to create the solution. 

[Anonymous]

The simplest idea is to create relatianship between thoose table a "Store name" column link. Create table and drag columns in one table on dashboard.

Second idea is to merge queries in Power query as column "Store name" to use as shared link between them

[BrunoNM]

When creating the table and dragging the columns, the rows that do not have data do not appear

[Anonymous]

Open model window and take a screenshot of created relationship. Check what columns are linked together

[BrunoNM]

The relationship is correc

I need that in this table, the stores that did not count on 10/01 and 10/02, appear anyway

[serpiva64 · Accepted solution]

Hi,

depending you are summarizing balance you need to check "Show items with no data"

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

[Anonymous]

You dragged "date" column to table from third table with unknown data and linked relation. What is the purpose of it if you want in your table "Date of inventory"?

[BrunoNM]

I used the column "Date" because it is in a calendar table, but if I drag a column "Date Inventary" the view continues to show only the counted days and I need it to show store, date even if there was no count.