Forum Discussion
summarize two tables
Hi,
I have two tables,
1. Month, newspaper company, distribution agency, distribution address (city), distribution address (street name)
2. Month, store name, customerid, customer address (city), customer address (street name)
I want to add "newspaper company" and " distribution agency" to the second table,
Problems:
1) Several newspaper companies distribute to the same addresses
2) Newspaper uses more than one agency and some use the same agencies
I tried to summarize them, but it gives me an error message that a table of multiple values was supplied where a single value was expected.
Can anybody help me with this?
8 Replies
Refer:https://radacad.com/append-vs-merge-in-power-bi-and-power-query
You can move the column from one table to another
New Column = maxx(filter(table2,table2[Col1]= table1[col1] && table2[Col2]= table1[col2] ),table2[required_col])
New Column = maxx(filter(table1,table1[Col1]= earlier(table1[col1]) && table1[Col1]= earlier(table1[col2]) ),table1[required_col])calculatetable is another option example. You can filter one table by the second table based on need
sumx(summarize(calculatetable(TableA,DATESINPERIOD( DATE[DATE], MAX( DATE[DATE] ), -6, MONTH )),
TableA[ID] , "_active",distinctcount(TableA[ID])),_active)thanks for your answer. "maxx(filter('table1...." almost worked!
I get the newspaper column, but it only shows for one streetname one newspaper company.
But it has to show the streetname several times, in order to show all the newspaper companies.
streetname A newspaper company A streetname A newspaper company B streetname A newspaper company C Anonymous Thanks for your answer, "related" does not work. error message reads, that there is no connection in this context. (I made a connection with date), I also tried "lookupvalue" before, but error shows multiple values where single value expected
Anonymous ,
I need to try with data.Can you share sample data and sample output.
mark me @
Appreciate your Kudos.
Anonymous
Summarize is a table function used to create a Table, if you just want to move columns to another table, a column function is expected.
In this case, you should have a relationship between the tables using Month columns, so you can just use Related() to create 2 columns in Table2.
Column 1 = RELATED('Table1'[newspaper company]) Column 2 = RELATED('Table1'[distribution agency])
Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
