Forum Discussion
Power query editor: index column and duplicate table
Hi!
If I add an index column to my table in query editor and then create a duplicate of that table, can I be sure that the indexing will always be coherent between the two tables?
Here's an example of what I'm trying to achieve, if it helps to understand the situation:
in the original table I have data like this:
Event Start date End date Index
abc 1/1/2018 1/3/2018 1
def 1/6/2018 1/7/2018 2
In the duplicate table I will then organize the data a bit differently:
Event Start date End date Index Dates
abc 1/1/2018 1/3/2018 1 1/1/2018
abc 1/1/2018 1/3/2018 1 1/2/2018
abc 1/1/2018 1/3/2018 1 1/3/2018
def 1/6/2018 1/7/2018 2 1/6/2018
def 1/6/2018 1/7/2018 2 1/7/2018
(If you're interested how this is done, here's the link: https://community.powerbi.com/t5/Desktop/Getting-all-dates-between-2-dates/td-p/60577)
There can be multiple events with the same name, so I need the index column to create a relationship between these two tables.
Thanks for any help!
If you are using the Table.ExpandListColumn function then the index will stay the same and be copied as many times as the dates between start date and end date
4 Replies
- jhhuNew Member
Hi!
If I add an index column to my table in query editor and then create a duplicate of that table, can I be sure that the indexing will always be coherent between the two tables?
Here's an example of what I'm trying to achieve, if it helps to understand the situation:
in the original table I have data like this:
Event Start date End date Index
abc 1/1/2018 1/3/2018 1
def 1/6/2018 1/7/2018 2
In the duplicate table I will then organize the data a bit differently:
Event Start date End date Index Dates
abc 1/1/2018 1/3/2018 1 1/1/2018
abc 1/1/2018 1/3/2018 1 1/2/2018
abc 1/1/2018 1/3/2018 1 1/3/2018
def 1/6/2018 1/7/2018 2 1/6/2018
def 1/6/2018 1/7/2018 2 1/7/2018
(If you're interested how this is done, here's the link: https://community.powerbi.com/t5/Desktop/Getting-all-dates-between-2-dates/td-p/60577)
There can be multiple events with the same name, so I need the index column to create a relationship between these two tables.
Thanks for any help!
- v-piga-msft
Resident Rockstar
Hi jhhu,
If I understand your requirement correctly that you want to create the relationship for the two tables?
If it is, by my tests, you could create the one to many relationship with index like below.
Best Regards,
Cherry
- v-piga-msft
Resident Rockstar
Hi jhhu,
Have you solved your problem?
If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please feel free to ask.
Best Regards,
Cherry
- LivioLanzo
Solution Sage
If you are using the Table.ExpandListColumn function then the index will stay the same and be copied as many times as the dates between start date and end date