Column in table has duplicate value error prevents applying the query

[Anonymous]

I am getting the following error after grouping and filling down missing data in step "Custom1", then expanding the data in step "Custom2".

The error is:

Column 'PMS-ID' in Table 'Fill Missing Dev' contains a duplicate value '724' 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.

My query is:

let
    Source = hPMS_HP,
    #"Merged Queries" = Table.NestedJoin(Source, {"PMS-ID"}, fConsolidatedPDA, {"Projekt-Id"}, "fConsolidatedPDA", JoinKind.LeftOuter),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Prio", "I / V / P", "Projekttyp"}),
    #"Expanded fConsolidatedPDA" = Table.ExpandTableColumn(#"Removed Columns", "fConsolidatedPDA", {"Exportdatum", "Projektkosten (Geplant) (MFr)", "Projektkosten (Ist) (MFr)", "Projektkosten (Verfügt) (MFr)"}, {"Exportdatum", "Projektkosten (Geplant) (MFr)", "Projektkosten (Ist) (MFr)", "Projektkosten (Verfügt) (MFr)"}),
    #"Sorted Rows1" = Table.Sort(#"Expanded fConsolidatedPDA",{{"PMS-ID", Order.Ascending}, {"Exportdatum", Order.Ascending}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Sorted Rows1",0,null,Replacer.ReplaceValue,{"Projektkosten (Geplant) (MFr)", "Projektkosten (Ist) (MFr)", "Projektkosten (Verfügt) (MFr)"}),
    Custom1 = Table.Group( #"Replaced Value1", {"PMS-ID"}, {{"Rows",each Table.ReverseRows(Table.FillDown(Table.ReverseRows(Table.FillDown(_, {"Projektkosten (Geplant) (MFr)", "Projektkosten (Ist) (MFr)", "Projektkosten (Verfügt) (MFr)"})), {"Projektkosten (Geplant) (MFr)", "Projektkosten (Ist) (MFr)", "Projektkosten (Verfügt) (MFr)"} )) , type table [#"Projektname"=text, #"PMS-ID"=number, #"Projektkosten (Geplant) (MFr)"=number, #"Projektkosten (Ist) (MFr)"=number, #"Projektkosten (Verfügt) (MFr)"=number]}}),
    Custom2 = Table.ExpandTableColumn( Custom1, "Rows", {"ID","Exportdatum", "Projektkosten (Geplant) (MFr)", "Projektkosten (Ist) (MFr)", "Projektkosten (Verfügt) (MFr)"}, {"ID","Exportdatum", "Projektkosten (Geplant) (MFr)", "Projektkosten (Ist) (MFr)", "Projektkosten (Verfügt) (MFr)"} )
in
    Custom2

In other topics, I read the solution is to change the cardinality to one-to-many, but I don't have any relationships. (at least, ones that I can find). 

Data (before transformation):

Data (after transformation): 

It is correct that PMS-ID has duplicate values in the data and it should not be a primary key. The value "724" is not the only duplicate and neither the first in my data. I have not been able to apply my changes to the query due to this error message.

How can I fix this?

[Anonymous · Accepted solution]

I solved the problem by applying the following steps:

1. Create an index column from "Add Column" tab.
2. Duplicated "PMS-ID" with new name "PMD-ID Copy"
3. Deleted "PMS-ID2
4. Renamed "PMS-ID Copy" to "PMS-ID"

It seems that Power Query applys a primary key to the "PMS-ID" column when expanding. By following those steps, Power Query will be forced to look for another primary key candidate, which is the newly created Index. 

The above statement is my interpretation and is not based on any documented information. Therefore, use it with caution.