Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have 2 related tables
I wish to add a new column to one of them which adds a defaul value based on the value of the related table.
i.e
Table1.newcolumn
if Table2.columnA = null, then new "New value" else "Another value"
In the custom table editor, it only provides columns from the table i am adding the colun to, and does nopt list the related colum,ns in the re,ated tables?
Any ideas?
Thnx
Jeff
Solved! Go to Solution.
Hi @uthall,
dkay84_PowerBI's point seems great, I'd like to share you a sample about use other table's value as the default value. (power query).
Below is the sample.
Formula:
defultValue= List.First(Table.SelectRows(Table.SelectColumns(Source,"ColumnName"),each _ <> null)[ColumnName]), #"Replaced Value" = Table.ReplaceValue(#"Previous Steps",null,defultValue,Replacer.ReplaceValue,Table.ColumnNames(#"Previous Steps"))
Table:
Result:
Full query:
let Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\test.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Desc", type text}}), defultValue= List.First(Table.SelectRows(Table.SelectColumns(Sheet2,"Amount"),each _ <> null)[Amount]), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","Dsvl",defultValue,Replacer.ReplaceValue,Table.ColumnNames(#"Changed Type"))//devl is the value which need to replace in #"Replaced Value"
Regards,
Xiaoxin Sheng
Hi @uthall,
dkay84_PowerBI's point seems great, I'd like to share you a sample about use other table's value as the default value. (power query).
Below is the sample.
Formula:
defultValue= List.First(Table.SelectRows(Table.SelectColumns(Source,"ColumnName"),each _ <> null)[ColumnName]), #"Replaced Value" = Table.ReplaceValue(#"Previous Steps",null,defultValue,Replacer.ReplaceValue,Table.ColumnNames(#"Previous Steps"))
Table:
Result:
Full query:
let Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\test.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Desc", type text}}), defultValue= List.First(Table.SelectRows(Table.SelectColumns(Sheet2,"Amount"),each _ <> null)[Amount]), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","Dsvl",defultValue,Replacer.ReplaceValue,Table.ColumnNames(#"Changed Type"))//devl is the value which need to replace in #"Replaced Value"
Regards,
Xiaoxin Sheng
You can still refer to other tables/queries, they just aren't given in the list. You have to tell the custom formula the table and column (and row number if necessary) that it should look at for your condition. I believe the syntax will be #"Table"[Column]{row}
User | Count |
---|---|
97 | |
87 | |
78 | |
74 | |
70 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |