In power query language(M language) how can we add custom “value” and “table” columns to a table manually?

Question

In power query if we get data from an sql database, "Value" and "Table" columns are created automatically if there are relationships in the database.

AFAIK "Table" and "Value" means one-to-many and many-to-one relationships respectively.

My problem is that there are no relationships in our database. So PowerQuery cannot generate these columns automatically. How can I manually add these columns if I know the relationships between the subject tables?

I found Table.NestedJoin function which returns Table object(but with low performance, even though there are relationships in the database.)

But I could not find any function which returns a Value object(record of another table).

Possible other solutions with flaws are;

You may advise that I get the tables as in the database and create relationships in Relationships section in Power BI(or in power pivot section in Excel). But I need this Value object in power query because I would like to filter the rows according to the related table before loading all the rows of the table.
Creating a native query which joins the tables which is not my preference.
Creating Table object instead of a Value object(we are sure that only one record will come.) Still I have a performance problem with Table.NestedJoin method. Is there another option?

Thanks in advance...

1- Out of curiosity, what's the datasource? Why not just add some foreign keys (in case it's a relational database) 2- You can call Table.First(table, nullable defaultValue) to make it a record if you know there's only 1 row 3- I was about to recommend you to add a custom column that uses native query, but then I saw that it's not your preference :) 4- NestedJoin's performance would be bad if we can't fold the query against the database, since we don't know they're actually related, we download all of the rows. This is why native query would be better for this case. — Oğuz Yıldız
Did you use JoinKind.Inner (0) during the nested join? This should translate as a WHERE-clause back to the server. With standard settings it works as a LeftOuter. If you want to reduce the returned datasets further, apply filters to the join tables first before performing that InnerJoin. (Edit: In case you've read the post I've deleted already: That was the perfect example of how not to do as it would not fold (due to the Table.Buffer) and be slow without the buffer, because it would by definition iterate through all rows and perform a comparison against the "lookup-table") — ImkeF
@OğuzYıldız Datasource is an sql server database. But this database has been designed long time ago. And (up to now) they rejected to define relationships in the database. I didn't know Table.First function. I think it would serve my needs. And I will read about query folding. Thank you. — serdar

Eugene Eugene · Accepted Answer · 2016-07-11T20:04:09

Just today I had quite same issue with performance, but finally solved it. In my solution I work with views, but need to filter records coming.
When I use such a code:

let
    filter1 = 2016,
    filter2 = "SomeText",
    tbl = Sql.Database("MyServer","MyDB"){Schema="dbo",Item="MyTableOrView"}[Data],
    filteredTable = Table.SelectRows(tbl, each ([field1] = filter1) and ([field2] = filter2))
in
    filteredTable

it works slow. But if I try NestedJoin - it performs much better.

let
    Source = Table.FromColumns({{2016}, {"SomeText"}}, "filter1", "filter2"),
    tbl = Sql.Database("MyServer","MyDB"){Schema="dbo",Item="MyTableOrView"}[Data],
    filteredTable = Table.NestedJoin(tbl, {"field1", "field2"}, Source, {"filter1", "filter2"}, "NewColumn", JoinKind.Inner)
in
    filteredTable

However, I noticed that even fastest design I got works slower than just a query that returns all ~~1300 rows from the view.

I have no SQL Profiler to track down what is exactly sent to the server, but it seems to me that query folding work when you use inner joins.

Try following: make 2 queries to 2 tables (no other actions!) and inner join them, then see if it works faster.

In power query language(M language) how can we add custom “value” and “table” columns to a table manually?

1 Answers