Relate Tables Function Without Need for A Sum

[W2SANC]

Hello, 

I have two tables (Table 1: List of State and County FIPS codes which the data is a combined into 5 digit number) and Table 2 (location of each sale transation by "county, state." There is a realtionship in my model tap that is a 1 to many (i.e. - many sales could have taken place in a single state/county FIPS code). 

I would like to be able to have the end result of adding a new column or table (doesn't matter which, I just need it for exporting purposes) that will spit out the sale (rows from table 2) with the 5-digit state and county FIPS code in addition to that. 

Is this possible via the RELATED function? I've read online you can get a sum or a filter, which I don't need either. I would just like to be able to have the two either in their own table (each row of Table 2 - each row is a sale) and a colunm with the 5-digit County/State FIPS code. I can't do a GROUPBY function since they are in different tables.  

Is this possible? 

Thanks, 

[marcelsmaglhaes · Accepted solution]

Hello @W2SANC !

I will assume you have two tables: FIPS and Sales, and them are relatede by a column, in my case, column County estabilished the relationship, and you want put the FIPSCODE into Sales table. If I understood correctley, so, look the sequence bellow and if it helps, please mark this post as solved.

If I misunderstood, please, give more details about you want to do.

Take a look in my tables:

To "put" the FIPCODE into Sales table, in Power Query you can use the Merge Queries function. Choose that option and select the column keys in the tables (in my case, column County is the key). 

After that, click OK and then expand the column FIPS

The result is a NEW TABLE that contais the sales table column plus the FIPCODE column from the FIPS table.

[W2SANC]

Hello, I miscommunicated on my part, very sorry about that. 

I have a sales table (each row is a sale transaction) with the last column "County, State" (ex: Marion County, Florida). I want to add a column after this that reflects the StateCounty FIPS Code (a 5 digit number identifier for every county in the US). I have this list in another table. In my modeling tab, I have a relationship set to active between the State County FIPS Code table (1) to the Sales Table colunn "County, State." (many). Cardinality is 1 to many. 

I would like to be able to add this column, and then a secondary question I will have is how to export the entire data set (not just the first 1,000 rows) out of Power BI into an excel or CSV file if I do not have a specific visual for this data, I just want to pull it for other purposes. 

Thanks

[marcelsmaglhaes]

Hey @W2SANC !

So, to put the FIPS code into sales table you can follow that sequence I gave you.

After create that new table, you can load to power BI desktop and put the data into a table visual and export them.

Hope that helps you.
Regards,
Marcel