Column contains blank values and this is not allowed for columns on the one side of a many-to-one...

[Lucian]

Hello,

I have a strange problem when I try to get some data from a single table:

Failed to save modifications to the server. Error returned: 'Column 'Enddate only' in Table 'PBI_Projects' contains blank values and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table. '.

let
    Source = Dynamics365BusinessCentralOnPremises.Contents("server.com:7148/.../ODataV4/", null),
    ConnName= Source{[Name="ConnName"]}[Data],
    PBI_Projects_table = ConnName{[Name="PBI_Projects",Signature="table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(PBI_Projects_table,{{"Starting_date", type datetime}, {"Ending_Date", type datetime}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Starting_date] >= RangeStart and [Starting_date] < RangeEnd),
    #"Inserted Stdate only" = Table.AddColumn(#"Filtered Rows", "Stdate only", each try DateTime.Date([Starting_date]) otherwise #date(1900, 1, 1), type date),
    #"Inserted Enddate only" = Table.AddColumn(#"Inserted Stdate only", "Enddate only", each try DateTime.Date([Ending_Date])  otherwise #date(1900, 1, 1), type date)
in
    #"Inserted Enddate only"

I have disabled the "autodetect relationships" even is the one and only table.

The problem it seemd to be created by the last two lines from the query where I need to extract only the date part from the datet/time columns.

Without that two lines works perfectly, but with them, I have that error message. Is not from the "try... otherwise" section because initially were not used and the error is the same.

How can I get another date only column based on the date/time column?...

Kind Regards,

Lucian

[Lucian]

Hello,

And thank you all for your responses.

@V-lianl-msftIndeed is somehow unclear so I try to explain a little bit more.

The table involved is the Navision's Projects table and the columns "Starting_date" and "Ending_Date" are date/time/timezone type.

All the problems started because I wanted to test the "Incremental refresh" in Power BI Pro that require 2 specific date/time parameters RangeStart and RangeEnd, so in the query I have changed type of the original column to date/time. Then I realized that I need just a date only column to relate to a dimDate table to make other calculations work.

The error message could arise because I'm trying to convert a date/time column to a date only column... but the error message is not pointing to the right direction. 🤔

Until now @Greg_Deckler 's tip was most usefull, so thank you Greg. 👍

Even I would like to do it in the query, using the DAX for the transformation outside the query will also help preserving the "query folding" that is also required for incremental refresh. 😉

So the problem it solved, at least for now.

Kind Regards,

Lucian

[V-lianl-msft]

Hi @Lucian ,

It is very difficult to analyze without looking at the data and just by imagining. See if you can share sample data and expected output.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Lucian ,

Not sure I got it completely.

you can try Date.FromText("2010-02-19") // Date, yyyy-MM-dd 

refer:https://docs.microsoft.com/en-us/powerquery-m/date-fromtext

[Greg_Deckler · Accepted solution]

Well, if you can do it in DAX, just use something like:

New Date Column = DATE(YEAR([Date]),MONTH([Date]),DAY([Date]))

Not sure why you are getting that error in your Power Query code.