Creating a table of columns from another table

[Coffeinalytics]

Hi, I just started learning power BI and I have a question that might seem silly but I would appreciate if anyone could help me.

So, I have a table that has too many information, and some columns, even tho they are related, would be easier to read if they were in a different table.

My idea was to create a "details" table from columns that can be quantified, like "price", "miles", "driver" and I was able to achieve what I wanted using "reference", however, I wonder if there is a way to reach the same results with coding.

Also, if I was to create a relationship between both tables, Should I create an index column in the original table and then use it as a foreign key in the new table?

I don't know if my logic is broken, but I hope you guys can understand.

Thank you!

[Bipin-Lala · Accepted solution]

Hi @Coffeinalytics,

One method you have already mentioned for creating a table out of another table is reference or duplicate in query editor. Then you can Choose Columns from the reference/ duplicate table and proceed

Another method is to use DAX in Data tab to create a table with specified columns.

https://dax.guide/selectcolumns/

https://learn.microsoft.com/en-us/dax/selectcolumns-function-dax

Though doing such transformation in M code/ query editor is usually faster and more beneficial than using DAX.

[amitchandak]

@Coffeinalytics , Power Query Duplicate the table remove a column, and remove the duplicate to create a header. Add an index column in this header table

Use the merge table to join the header with details with no keys and populate the index column to join in the Power BI model.

Throwback- Power BI: Power Query Functions one must know
: https://www.youtube.com/watch?v=cN8AO3_vmlY&t=36407s
Power BI - Cheat Sheet: Power Query Functions one must know
https://amitchandak.medium.com/cheat-sheet-power-query-functions-one-must-know-a8531b6e321a