Forum Discussion
How to generate text value to another table?
Hi. I have 2 tables with me. Table A and Table B. Both tables have a Year column, but only table B have a Month column. How can I generate the Month column from table B to Table A? So that I have Month in Table A too. By the way, Month's data type is in text.
Hi Anonymous ,
Solution 1 You can Merge Queries in Power Query Editor to generate the Month column for the table without Month column.
The result looks like this:
Solution 2 You can create a Calculated table with CROSSJOIN function to join the two tables together and this table will contain the Year and Month column.
Table =
FILTER (
CROSSJOIN (TableB, TableC),
TableB[Year1] = TableC[Year]
)
The result looks like this:
Best Regards,
Caiyun Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 Replies
- amitchandak
Super User
Anonymous , do they have something common to copy value?
You can copy the value from one tablet to another like https://www.youtube.com/watch?v=czNHt7UXIe8
or can create date like
From year
Date = date([Year],1,1)
From year and month no
Date = Date([year], [month],1)
From year and month name
Date = "01-" & [Month] &"-" & [year] //Change Datatype as date
Join both with date table
amitchandak The only thing common between those 2 tables is a year. I can't create a month as you suggested because my Year column is a fixed number, so if I create Data, it will only show January. When I try Related(as in your video), they're also not working because of different types of relationship. And this happens when I try LOOKUPVALUE
- PhilipTreacy
Hi Anonymous
Without any relationship/link between the tables there's no way to know how to copy a month from one table to another.
How is the code going to know which month to copy/create in Table A?
There must be some logic/rule that indicates what month to create in Table A.
If you have 12 rows for each year then that way you can at least insert the months Jan-Dec.
Can you provide some sample data?
Regards
Phil
PhilipTreacy amitchandak yes i understand it seems impossible to create month just like that. I'm attaching my pbix file.
Both of the table already had a many-to-many relationship, connected by Year.
According to my pbix file, I have 2 charts. Both are from different tables. The second chart only changes when I'm using Year slicer. Since the other chart and all the slicers are from table 'By Well', I was thinking to move whatever the second chart needs to another table( which is table 'By Well'). The problem is, I cant seem to move Month. I cant seem to think of other way.
What I really want is for the second chart to change when I click PAC slicer. That's all.
https://1drv.ms/u/s!AoKh3nlpMpSOgmJclyavi_-i2foE?e=h26awm
v-cazheng-msftCommunity Support
Hi Anonymous ,
Solution 1 You can Merge Queries in Power Query Editor to generate the Month column for the table without Month column.
The result looks like this:
Solution 2 You can create a Calculated table with CROSSJOIN function to join the two tables together and this table will contain the Year and Month column.
Table =
FILTER (
CROSSJOIN (TableB, TableC),
TableB[Year1] = TableC[Year]
)
The result looks like this:
Best Regards,
Caiyun Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-cazheng-msft I have tried the first solution and It works perfectly fine! thank you! but I cannot filter by PAC? It wont work.
- v-cazheng-msft
Hi, Anonymous
Sorry for the late reply. And if you want the second chart change by clicking PAC_ALIAS, you can add PAC_ALIAS to the Column series box of the second chart. In fact, there is no need to move Month to the table ‘by well’.
The result looks like this:
Best Regards,
Caiyun Zheng
Do these solutions work for you? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
