Power query editor: index column and duplicate table

[jhhu]

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!

[jhhu]

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]

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

[v-piga-msft]

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

[LivioLanzo · Accepted solution]

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