Conditional merge/add custom colum with table

[Hanspw]

Hi.

Here is my data tables:

"Datagrunnlag" is the fact table. which contains many transactions which are registered on both company code and project code. I then have 10 more columns with information in two different dim_tables (dim_prosjekt and dim_selskap (company)), the same columns exist in both dim_tables, however one dim_table (dim_prosjekt) also conaints more information. The fact table (datagrunnlag) is linkes to the two dim_tables with company_ID and project number.

On some companies i have registered the "tags" in the "dim_selskap" table, but for other companys the tags need to be done by project number. So in this companies the "tags" are registered in the dim_prosjekt table.

What i need to do now is to merge this 10 data fields from the dim_tables together to 10 new colums in the fact Datagrunnlag table, so that i can get complete values in the fact table.

I have searched for different solutions, by conditional merge, and i also found this topic:

https://community.powerbi.com/t5/Power-Query/Conditional-Merge/m-p/844207#M28332

This seemed like a good solution:

• do both merges first, expand those tables to colums, make new custum colums to combine the colums, and then delete the old colums.

However that would be easy with 1 column. But now i have 10 colums (which could be more later). I then realised i had to to this calculations for each column, and also add do it manually again if i get more colums in the dim_tables. So i tried another solution:

• Do both merges first, but dont expand the colums. 
• Then insert a new custom column to combine the tables before doing the expand
• Then i hoped to get the combined data in the new table in the new custom table, and that i then could expand the new combined data.

It seems like this should work, because the new custom column seems to give me a new table in the new colum. However i struggle to find the right if formula when i add the custom colum.

So after i have done the two merges i have the two new table colums:

if [#"dim_selskap (z-ark)"] is null then [#"dim_prosjekt (z-ark)"] else [#"dim_selskap (z-ark)"]

Which is obviously wrong. But it gives me a new cusom column with table. The table is always from dim_selskap, because the "is null" part is wrong. But since it gives me a table i was hoping this could work.

What i was hoping to do was to change the formulat to something like this:

if "[#"dim_selskap (z-ark)"].column(use_project_relation) is "Yes" then [#"dim_prosjekt (z-ark)"] else [#"dim_selskap (z-ark)"]

The.column part is wrong, i dont know what to write to get this to work. Or if it would work at all. Hope someone could guide me in the right direction. Would this work or is it better to do it another way?

Thanks

Hans Petter

[lbendlin]

Yes. Send two versions so we can see the dynamic column structure change.

[Hanspw]

Hi, i sent you the model in a private messag, did you receive it?

[lbendlin]

I did.  Haven't had enough time to think about it. Maybe over the weekend. Work is crazy at the moment.

[Hanspw · Accepted solution]

Ok, i think i finally understood what you meant.. I now did this:

• Combined dim_project and dim_company to one single table. In this table i made new ID keys which consisted of projectnumber for projects and company nr for companies.
• In fact table i made a new column and then used dax code to make this same id_key, so for some companies ID key was projectnumber and for other companies ID key was company number.

I now have a relationship with all values, which was what i wanted, and the refresh on the dataset takes 15 seconds instead of 15 minutes 🙂

Now i just need to find out how i can combine relationships, which can change over time.. so that the releationship is different lets say in 2020 than it is in 2021...

[Hanspw]

Dont worry about it, just happy that you take the time to help a poor guy. 

[lbendlin]

I would construct the additional tables in Power Query. That way you can shape them dynamically (with the columns you need) and use them in the Power BI data model for your hierarchies.

[Hanspw]

Thank you but i have a little trouble to understand what it the outcome of this. Could you explain in more detail?

• How would this new tables look. Would there be new separate tables from the current fact and dim tables? 
• Will there be one new table or more new tables?
• How will the fact table be connected to the new tables, to be able to put one filter on "business unit" in the reports?

Maybe hard to explain. If i send you my datafile could it be easier to show me?

[Hanspw]

Found a solution myself:

• Merged dim_selskap
• Expanded only column "use_project_relation"
• Then merged dim_selskap and dim_prosjekt again
• Made a conditonal new column, if "use_project_relation" = "Yes" then [#"dim_prosjekt (z-ark)"] else [#"dim_selskap (z-ark)"]
• This gave me a new column with a conditional combined table which is what i needed. 
• I could then expand this new column and get all combined values.
• Only thing is when i use expand on the new combined column, it only expand columns which are identical in the two dim_tables, and not the one which exists only in the dim_prosjekt table. I would like it to expand all columns. However its not critical, i guess i could add the same columns in dim_selskap just to get them in the same operations, or i could do another merge on this columns afterwards. But if someone know how to do the expand on all columns please let me know.

[lbendlin]

Power Query has functions to get table columns. If your first row are the header columns then you grab that and then use it for the expand transform.

[Hanspw]

Yeah that was what i was trying to do yesterday, but i did not know the right formula 🙂 However expanding the column and then doing the if formula bases on column value seems to work.

The only thing is that the expanding column is making my datamodel take forever to update... Thats with 30000 rows of data. It takes maybe 10-15 min to update. Didnt know this function was that slow.  Vlookup in excel does this in seconds on the same data 😕

[lbendlin]

---

@Hanspw wrote:

Yeah that was what i was trying to do yesterday, but i did not know the right formula 🙂 However expanding the column and then doing the if formula bases on column value seems to work.

The only thing is that the expanding column is making my datamodel take forever to update... Thats with 30000 rows of data. It takes maybe 10-15 min to update. Didnt know this function was that slow.  Vlookup in excel does this in seconds on the same data 😕

---

Power Query Merge is one of the costliest operations as it kills both the preview and the query folding. That is why I suggested using additional dimension tables instead.

[Hanspw]

Ok thanks, good to know. Could you possible try to explain how i can use additional dimension tables instead of doing the merge? The goal would be that i need to combine those dimensions fromt he two dim tables, into a combined field which i can use in my reports.

• Company 1,2,3 i want to define into a "business unit" based on company code, fom table dim_company
• Company 4 and 5 need to be split into different "business units" bases on company number, fom table dim_project
• Business unit X would then be a combination of company 1 and 2, and some projects in company 4 and 5, while business unit Y would be a combination of company 3 and some projects in company 4 and 5.
• I want to in my reports show total profit/loss for each business units.

In excel i did this by a combination of if fomula and vlookup from the two tables.

[smpa01]

@Hanspw  this is an interesting problem. 

Can you please try the following for a fully dynamic conditional merging solution

//tableName-dim1
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs7PLTBU0lFKycw1VIrVgQgYQQSMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [comp = _t, dim = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"comp", type text}})
in
    #"Changed Type

dim1

//tableName-dim2
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs7PLTBW0lFKycw1VorVgQiYQARMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [comp = _t, dim = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"comp", type text}})
in
    #"Changed Type"

dim2

//output
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs7PLTBUitWBsIzgLGM4y0QpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [comp = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"comp", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "fromdim1", each List.Contains(dim1[comp],[comp])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "formdim2", each List.Contains(dim2[comp],[comp])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each if [fromdim1]=true then dim1 else dim2),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.1", each let 
val= [comp],
x = [Custom], 
y = Table.SelectRows(x, each([comp]=val)),
z = Table.SelectColumns(y,{"dim"}) 
in 
z),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom3",{"comp", "Custom.1"}),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom.1", {"dim"}, {"dim"})
in
    #"Expanded Custom.1"

master

in

[lbendlin]

"
What i need to do now is to merge this 10 data fields from the dim_tables together to 10 new colums in the fact Datagrunnlag table, so that i can get complete values in the fact table."

Or you could add more dmension tables and let the data model do the work for you.

[Hanspw]

Could you elaborate what you mean here? Do you mean to combine the two dim tables to a new dim table or something else? I dont see how this would solve my problem to get "one" complete relation to the fact table?