Expanding tables in Power BI across multiple columns simultaneously

[Raalu]

I have a query with multiple columns horizontally. Each column contains a table with 28 rows, with each row across each column relating to the same index (.e.g Row 1 of the Quarter Table relates to the same index as Row 1 of the Date Table.

 Is there a way to expand each column simultaneously so that the rows are properly aligned by index? I've tried to expand them using the expand icon but instead only the first column expands correctly (1 row per item) but when I expand the next column's table, it duplicates the rows for each table item against the first column (e.g. 28 rowsfor each of the 28 items from Column 1) rather than 1 item for 1 row throughout.

Index	Quarter	Date
1	Value at Index 1	Value at Index 1
1	Value at Index 1	Value at Index 2
1	Value at Index 1	Value at Index 3

In the end I want it too look like:

Index	Quarter	Date	Column X
1	Value at Index 1	Value at Index 1	Value at Index 1
2	Value at Index 2	Value at  Index 2	Value at Index 2

Any thoughts are helpful as I've hit a dead end / other solutions I have tried seem to crash Power BI.

[Anonymous]

Hi, @Raalu 

Start by creating a new Index for your table as a unique identifier, using the Merge queries into new queries option in Power Query instead of expanding the table directly. This allows you to join tables based on a common key, in this case, an index. Select the Inner Join type to ensure that only matching rows from both tables are included in the final output.

Once you have a merged table that aligns the rows by the indexes of both tables, you can expand the columns you want. Because the rows are aligned by an index-based merge operation, expansion should not result in duplicate rows.

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Raalu]

Hi,

Thanks for the response.

I don't quite understand / this doesn't seem to work. Are you able to run through the steps in more detail?

If I add an index to my query as I show it in the first image, it adds 1 row of index 1, like the below. Is this what you mean?

Index	Quarter	Date
1	Table	Table

If I expand this, everything has an index of 1 but before expanding it I used merge queries as new, selecting the index as the key but what am I merging against? Do I duplicate the query and merge against that?

I tried merging against itself, shown below, but it makes no difference, the resulting table still needs to be expanded.

Do I need to expand one of the columns first and apply the index so that it creates  an index 1-28. I tried this below too but the result was exactly the same - every other column doesn't expand correctly.

I appreciate all the help here!

Thanks