Forum Discussion
Conditional merge/add custom colum with table
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...
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.
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.
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 😕
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.
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.