How to make unique relationship between two table in Power Bi on left part of text

Question

As a rookie in Power Bi, Power Query and Dax I am stuck on how to make up a unique relationship between two tables when only left part of my key is known.

The first table is a result unique values of user inputs from one of the It systems.

I managed to add a column here that can calculate the Actual cost on all orders based on the left part of the WBS_ELEMENT like this:

Actual Cost = SUMX (FILTER ('SAP Orders', LEFT('SAP Orders'[WBS_ELEMENT], LEN([WBS in Uppercase])) = [WBS in Uppercase]),'SAP Orders'[ACTUAL_COSTS])

Question 1: How can I filter this Sap Order table in Power Query on the unique values by the left part of the WBS_ELEMENT? Note that the first table changes all the time, and it will grow in time. That means it need to filter on all values that BeginsWith.

Question 2: How can I make a unique relationship column in Sap Order table to be used as the relations when filtering is applied?

I have tried several ways to create the relation column like = Table.AddColumn(#"Changed Type", "Match", each Text.Equals([WBS_ELEMENT], uWBS[Used_WBS])) without any luck.

Example of my problem and some table values here:

The second table is of SAP Orders, and is quite large. The best would be if I could use the first table to filter out numbers of records in Power Query, so I can select more columns and reduce my dataset a lot. But I can`t find a good solution here either to filter on the left part of these unique input values.

Description of my problem: Column Used_WBS is added to the table in Power Query M - and is to be used as the unique relations on any given WBS in the Distinct_ERP_PROJECT_ID table.

Why is Used_WBS = null on SS-B33813-30-A-08-06-100 as the only order containing the full WBS path ?

According to the first answer I have tried:

MergeWBS = Table.AddColumn(#"Kilde", "Join", each Table.SelectRows(Distinct_ERP_PROJECT_ID, (uWBS) => Text.StartsWith(_[WBS_ELEMENT]&"x", uWBS[Used_WBS], Comparer.OrdinalIgnoreCase))),

and without the &"x" (added just to check if Text.StartsWith return false if equal). I have also tried this:

MergeWBS = Table.AddColumn(Kilde, "Join", each Table.SelectRows(Distinct_ERP_PROJECT_ID, (uWBS) => Text.PositionOf(_[WBS_ELEMENT], uWBS[Used_WBS], Occurrence.First, Comparer.OrdinalIgnoreCase) = 0))

Answer 1

My problem was actually solved early on by Sam Nseir, but it was very hard to actually spot all the spaces at the end.

The temporary solution was to add a step to trim the Used_WBS column. Right Click on the column and select Transform - Trim made up this step to clean up the input data.

= Table.TransformColumns(#"Stop Folding",{{"Used_WBS", Text.Trim, type text}})

Used_WBS is here a result of user inputs. So the one record I had my trouble with I copied the value and pasted it into Notepad. Where you can mark and se the blanks values as well.

Answer to Question 1

Perform a inner join with use of LIKE CONCAT(B.WBS, '%') on the source file in SQL, to reduce the amount of orders records, and perform the trim of the district WBS values. My Order query now looks like this:

    SELECT WBS_ELEMENT, ACTUAL_COSTS, ACTUAL_HOURS, ORDER_NUMBER, ACTUAL_RELEASE_DATE
FROM [[Our_Warehouse]].SERVICE_ORDER_HEADER AS A
INNER JOIN (SELECT DISTINCT trim(ERP_PROJECT_ID) as WBS FROM [[Our_Warehouse]].SERVICE_PROJECT_COST_ITEM_DATA WHERE ERP_PROJECT_ID <> '') AS B
ON A.WBS_ELEMENT LIKE CONCAT(B.WBS, '%')

The final solution was to make our developers to perform this trim of ERP_PROJECT_ID and switch to uppercase already in the app developed to takes this input field. Always best to clean up data before it reaches the data warehouse.

Answer to Question 2

Like described in accepted answer with these steps:

#"Stop Folding" = Table.StopFolding(#"Changed Type"),

MergeWBS = Table.AddColumn(#"Stop Folding", "Join", each Table.SelectRows(WBS, (uWBS) => Text.StartsWith([WBS_ELEMENT], uWBS[Used_WBS], Comparer.OrdinalIgnoreCase))),

#"Expanded WBS" = Table.ExpandTableColumn(MergeWBS , "Join", {"Used_WBS"}, {"Used_WBS"}),

Answer 2

For Question 1
In PowerQuery, in your SAP Orders query, in Advanced Editor, add the following steps: (replace << ... >> bits)

  MergeWBS = Table.AddColumn(<<PreviousStep>>, "Join", each Table.SelectRows(<<Unique WBS Query>>, (uWBS) => Text.StartsWith(_[WBS_ELEMENT], uWBS[Used_WBS], Comparer.OrdinalIgnoreCase))),
  #"Expanded WBS" = Table.ExpandTableColumn(MergeWBS , "Join", {"Used_WBS"}, {"Used_WBS"})
in
  #"Expanded WBS"

This should give you a new column in your SAP Orders query of the matched "Used_WBS".

For Question 2
With the above, you can now create a relationship between the two tables.

Additional - for demo
In a new PBIX, create a query for each of the below with their respective query name in the comment. (To this in the same order).

// SAP Orders
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XVXtbhw3DHyVwr8lgOKXxJ/21W7Q4Io4TpCgQd7/NTqkds/n4oA77Z4oDocz1K9fD29v/UnEjLrzQ3vAQrj5wnJwGzN/xxwr7OF3q92XN1prdqY+/hjRyfsgym1BZLza8Hwg4ia5kMlsq4IvwUPxxJKZhim1VYnarBczmIPPPE8ktrw7de40u0UPikS4xEbzzIJsXgjFYh5JjnqA7LK6deIuiW6SUDTLKPGWQUbTpsUZ9PqiSQJRj/yi0aMqEZmNBhYJtirSGWR6xl2vnwnwsF8SJuUW1NHIirrmu7S5cNBmAWRPciHyoFHYxFfjTXVbtZ8cDFLt/+vT92+P/O0rD3mk8ZJl05yzcaJzbVyJWJXW7tHjVXjOgbMXCVKMao4xtu5lkwohC5o3ui84fKwsHXUkb5VorWYFzDcJw8yXfuiRW1fpXqRZd9v4kbstLboa1yu0XoDlrkeIZEXko2SbuNCJyGqRgbK7BVUMm3Gf0TSTjcqIONrHI2fjg/OCoOrzpom/f16Rvv+8piayzUkeR4g0XiUlRcKqlM0OtV8EnDDjJLBbrQL1QU1Thww57R4Pj/AbQEY3I4GViCoKh0zbZlpNR4kv2bgFXZ6gKOwvHhFZroDcNqJ5iAMSWkJ3MTKrYaXyzYKjim1AKLBOcZhDP9BHq8/P6PKfVgB1m3c5NaqGrTYrUlT43favLxDg//yRWl9+NqqtPS6Madg9TGf+CFN0wvO2SZxafDgP89MgToRkZPBxotMpodvyqrvJ+F9dbobSF8b4+Sejqmi4HQysLV1wD8dpqWoQu7+7/nr99w2skyY6uLeEF9FibeJjN8CgpZvofxDgYVyudEoxQSUnDbgxyjd8WMyV17ihBPVZFcbfVgU0By8emTJzuNGJ7fXTM8ESEKCnYBai2y4g2g6JYN2MPeNUISZSEOYbDediDweQXL+BocPvDf2O/dy/fMV85VFOGpmApyrgWBnkcP8iDNRzumCkKPqDTxZTRsJ4GFGe1Yia6WHjrHy7fUiZoqy+y8cA9C1w2lq1nIp6pHkCW0ILMx+59w0jaPniDQ0GqikesuJuHg3JIcZx5KkbjWf47gvujRKPkCzmO3DLvQviukm3dV5ojGvF9i2Gq2ruaaSM4A3xWcb0L4NmYsSsLIwwJe1Bq2CvqMDFYqDi938=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WBS_ELEMENT = _t, #"ACTUAL_COSTS -" = _t, #"ACTUAL_HOURS -" = _t, #"ORDER NUMBER" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WBS_ELEMENT", type text}, {"ACTUAL_COSTS -", Int64.Type}, {"ACTUAL_HOURS -", Int64.Type}, {"ORDER NUMBER", Int64.Type}}),
    #"Stop Folding" = Table.StopFolding(#"Changed Type")
in
    #"Stop Folding"

// WBS
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dVLLbsQwCPyXnEHiZcDH7kr9gRxXe+on5P+lOvHGcaT2NniGETB+vZZ1xYcYW0VSZCRe4M+35Q1drJqsqIRfSInkyESfnn8oyUwHkcnCXUdPx1WVazoD+6l7PihKdN3Ag1ypuX7IE7NpGEgcom3bieAcoo5ZS1KBqjermKx2zBJm08SlELqMoUdZSooK8M2t1svtwPPq0u6iGPid1wlubzXYSwHjc42fjVKvNTrer2Vgk6ae4TVNvYW2N8VkcGAVMYd6xSLq1CKP8wdcpbiZC0SL5v0L", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Used_WBS = _t, #"WBS in Uppercase -" = _t, #"Actual Cost -" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Used_WBS", type text}, {"WBS in Uppercase -", type text}, {"Actual Cost -", Int64.Type}}),
    #"Stop Folding" = Table.StopFolding(#"Changed Type")
in
    #"Stop Folding"

// SAP Orders w StartsWith
let
    Source = #"SAP Orders",
    MergeWBS = Table.AddColumn(Source, "Join", each Table.SelectRows(WBS, (uWBS) => Text.StartsWith([WBS_ELEMENT], uWBS[Used_WBS], Comparer.OrdinalIgnoreCase))),
  #"Expanded WBS" = Table.ExpandTableColumn(MergeWBS , "Join", {"Used_WBS"}, {"Used_WBS"})
in
  #"Expanded WBS"

// SAP Orders w Position
let
    Source = #"SAP Orders",
    MergeWBS = Table.AddColumn(Source, "Join", each Table.SelectRows(WBS, (uWBS) => Text.PositionOf([WBS_ELEMENT], uWBS[Used_WBS], Occurrence.First, Comparer.OrdinalIgnoreCase) = 0)),
  #"Expanded WBS" = Table.ExpandTableColumn(MergeWBS , "Join", {"Used_WBS"}, {"Used_WBS"})
in
  #"Expanded WBS"