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