M Language Add New Column

[LobsterLegend]

New to PBI here. Attempting to combine data from 2 different tables in Query Editor. From what I've read, this seems a more appropriate place than would DAX. The first Table is Financial Statement Lines and the second is Financial Statemeant Headers (a super-category for lines). Very rookie question: when attempting to add a new column, I cannot reference a column from another table. It could be a simple syntax issue I am missing.

These are the Tables in reference.

Within GL FS Lines, I use Custom Column and attempt to use the Table.Column function to begin my formula. I can reference the other Table -- GL FS Headings -- just fine. But when I attempt to reference the column 'Header' from that Table, you can see that I am not given that option. Rather, I am given options for the current Table (GL FS Lines). Why can I not see any columns from the Table GL FS Headings?

Sampling of data in GL FS Lines:

Sampling of Data in GL FS Headings: (there's 2 columns for FS Line further to the right. It's a range so I'll have to use an additional function to account for that)

I appreciate your input.

[AlexisOlson · Accepted solution]

As @Nathaniel_C points out, in order to add columns from another table, you generally merge the tables and expand the columns you're interested in.

Joining on a range makes this significantly more involved. Radacad has a good article on a very similar problem:

https://radacad.com/dates-between-merge-join-in-power-query

The key here is to expand the header table into a table you can merge with. So just like Reza creates a list of dates, you can create a list of lines in a custom column like this:

For the first row, this creates a list of integers from 20 to 160.

Expand that column and then do a standard merge.

[LobsterLegend]

Thanks! Worked like a charm.

[Nathaniel_C]

Hi @LobsterLegend ,
Merge the two tables, and during that process only select the one column that you wish to add to your base table.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[HenriqueReis]

Hi,

Please, try to do a relation between those tables here, out of query:

Or try to merge those columns here, inside Power Query:

Regards!