· Jul 20, 2022

Data showing as NULL in Power BI when it shouldn't

Hello, I'm using the InterSystems IRIS connector for Power BI to connect to our Iris for Health database, using Direct Query.

I have a column [Company] which is datatype bigint, and as far as I can tell the data is 2 for most (or all?) records.

When I bring the data into Power BI, every row shows as NULL (I think it should be 2 for most)

= Table.SelectRows(Table, each [Company] <> null and [Company] <> "")

-- removes all rows

so instead I use

= Table.SelectRows(Table, each [Company] = 2)

-- the nulls magically become 2s (other numbers I've tried lead to an empty table)

but, out of the first 10  (eg primary key [ID] < 11), rows 7, 8, and 9 disappear -- I assume that [Company] isn't 2 for those.

But then just to see, my next step is 

= Table.SelectRows(Table, each [ID] < 11)

-- and rows 7, 8, and 9 reappear... with [Company] being 2!

Full M code:

    Source = IRIS.Database(****", ****, ***, "No", "No"),
    tables = Source{[Key="tables"]}[Data],
    ISS_Doc_Schema = tables{[Name="ISS_Doc",Kind="Schema"]}[Data],
    FinTranLine_Table = ISS_Doc_Schema{[Name="FinTranLine",Kind="Table"]}[Data],
    #"Sorted Rows" = Table.Sort(FinTranLine_Table,{{"ID", Order.Ascending}}),
    #"Filtered Rows1" = Table.SelectRows(#"Sorted Rows", each [Company] = 2),
    #"Sorted Rows1" = Table.Sort(#"Filtered Rows1",{{"ID", Order.Ascending}}),
    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows1", each [ID] < 11)
    #"Filtered Rows"


As [Company] is the foreign key for another table, I've also tried expanding that other table (which is joined onto the main table), and it is full of NULL until I can trick [Company] to change from NULL to 2, at which time it changes to data.

I've tried changing the data type.

I've tried adding custom columns which either copy [Company] or use an IF statement to reference [Company] but not copy it directly - it just changes when [Company] does.

I've tried Import instead of Direct Query - same results.


I've tried standard ODBC connection with an SQL statement, and [Company] is NULL and can't be tricked into changing to 2.


Please help my rapidly declining sanity... what is going on here??

Product version: IRIS 2021.1
$ZV: IRIS for Windows (x86-64) 2021.1 (Build 215_0_21002U)
Discussion (0)0
Log in or sign up to continue