Insert a manual row that contains TRUE/FALSE columns fails due to data type

[LeeMarsh100]

I have a table DimEmployees imported from SQL Server.  I need to insert a manual row for a fake employee, for reasons I won't go into here.  I do not have access to the source table to enter this row in SQL Server.  I am attempting to use Power Query's Advanced Editor to do this.  The problem I have is the last two columns are bit columns in SQL Server.  They display as a TRUE or FALSE in PBI.  I cannot add my new row because every value I attempt to enter is rejected as invalid for the data type.  Example:

let

    Source = Sql.Database("MyDB"),

    dbo_DimEmployee = Source{[Schema="dbo",Item="DimEmployee"]}[Data],

    AddFakeEmployee = Table.InsertRows(dbo_DimEmployee,0,{[Id=-1, FirstName="Fake", LastName="Employee", Status="False", IsDeleted="False"]}

in

    AddFakeEmployee

My problem is the last two columns are rejected because "False" is invalid for the column type.  Things I have already tried:

   Status=False    Status=0   Status=FALSE()   Status="False"  Status=null  Status=NULL  Status="Null"  Status=BinaryFormat.Null

I should note:  I don't care what the value is.  It could be True, False, Yes, No, 1, 0, or NULL.  The value is irrelevant to what I am doing, I just need the appended row to not draw an error.  Any help would be appreciated.

[MarkLaf]

Please share the error message to help troubleshoot.

If the column type is logical, then Status=false should work. Note that PQ is case sensitive and False <> false. If indeed you tried Status=False, this would not work and produce the following error.

It may also be helpful for you to check the table schema and share here. I.e.

= Table.Schema( dbo_DimEmployee )

It seems like maybe some issue unrelated to column type is occurring, as Status=null also should have worked (even if the column is nonnullable in source, I believe).

[tayloramy · Accepted solution]

Hi @LeeMarsh100 ,

You are running into M typing rules. In Power Query, booleans are the unquoted literals true and false (lowercase). Quoting them makes them text, which will not match the column's logical type, so Table.InsertRows rejects the row. Microsoft's docs call this out: the inserted row's column types must match the table, and logical values are written as true/false. See Table.InsertRows and M logical values.

Easy solution

• Make sure the two columns are typed as logical, then insert the row with false (no quotes).

More robust solution

• If your base table has many columns, a robust pattern is to build a one-row table with the right types and append it with Table.Combine. This avoids field-order headaches and automatically fills other columns with nulls. Reference: Table.Combine.

Code (Option A)

let
    Source = Sql.Database("MyServer", "MyDB"),
    dbo_DimEmployee = Source{[Schema="dbo", Item="DimEmployee"]}[Data],

    // Ensure types on the boolean columns (adjust names if needed)
    Typed = Table.TransformColumnTypes(
        dbo_DimEmployee,
        {{"Status", type logical}, {"IsDeleted", type logical}}
    ),

    // Insert a record with logical literals (no quotes)
    AddFakeEmployee =
        Table.InsertRows(
            Typed,
            0,
            {
                [Id = -1, FirstName = "Fake", LastName = "Employee", Status = false, IsDeleted = false]
            }
        )
in
    AddFakeEmployee

Code (Option B)

let
    Source = Sql.Database("MyServer", "MyDB"),
    dbo_DimEmployee = Source{[Schema="dbo", Item="DimEmployee"]}[Data],

    // One-row table with correct types for the columns you care about
    ManualRow =
        #table(
            type table [Id = Int64.Type, FirstName = text, LastName = text, Status = logical, IsDeleted = logical],
            {
                {-1, "Fake", "Employee", false, false}
            }
        ),

    // Append; any extra columns from dbo_DimEmployee will be null for the manual row
    Result = Table.Combine({dbo_DimEmployee, ManualRow})
in
    Result

Notes

• TRUE, FALSE, "False", 0, 1 will not work unless you explicitly coerce them. If you need to coerce, use Logical.From(0) or Logical.FromText("false"), but plain false is simplest. See Logical.From and Logical.FromText.
• null is valid for logical columns too, if that is preferable: Status = null.

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.