CODEWITHSUNDEEP
powerbipowerquery
serdar
serdar

Reputation: 1628

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

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. enter image description here

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;

  1. 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.
  2. Creating a native query which joins the tables which is not my preference.
  3. 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...

Upvotes: 3

Views: 2876

Answers (1)

Eugene
Eugene

Reputation: 1264

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.

Upvotes: 1

Related Questions