New Column where it queries value in related table

[uthall]

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

[v-shex-msft · Accepted 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

[dkay84_PowerBI]

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}