Dataflow error: Attribute collection contains non-unique values

[Anonymous]

Hi,
I'm using dataflows with PowerBI Pro and I get the following error message.

I can't find anything online about this error and in PowerBI desktop it loads just fine.
Within the query I'm calling an API but even if I don't call the API, I still get this message. 
I only get the error when naming the dataflow, so after I hit 'Save & Close'
Purging the whole table of duplicate rows is not really an option.

[Anonymous]

Hi @Anonymous ,

Can you please share more details information to help us clarify your scenario?(e.g. detail operation steps, query formula...)

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

[Anonymous]

Hi,

I seems to happen randomly, really.
Mostly when I am actively working on a dataflow and I chance the M query code a lot.
I get the error regardless of the types of M code I run. Below is the code of the query that I run. Sometimes this code make me get the error, sometimes it doesn't. My browswer is the the Edge Beta.
//DST test
let
Geocoding = (adres as text) =>
let
Source = Json.Document(Web.Contents("http://www.datasciencetoolkit.org/maps/api/geocode/",[RelativePath="json?sensor=false&address="&adre...""])),
results = Source[results],
results1 = results{0},
geometry = results1[geometry],
location = geometry[location],
#"Converted to Table" = Record.ToTable(location),
#"Transposed Table" = Table.Transpose(#"Converted to Table"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"lat", type number}, {"lng", type number}})
in
#"Changed Type",
Bron = Csv.Document(Web.Contents("https://vanbruggen.sharepoint.com/sites/ManagementInformatie/Gedeelde%20%20documenten/Excel 2.0/Afspraken/Rapportage Inbound 0800.csv"), [Delimiter = ";", Encoding = 1252, QuoteStyle = QuoteStyle.None]),
#"Headers met verhoogd niveau" = Table.PromoteHeaders(Bron, [PromoteAllScalars = true]),
#"Rapportages toegevoegd" = Table.Combine({#"Headers met verhoogd niveau", #"Rapportage Inbound Phonecare", #"Rapportage NHB", #"Rapportage Outbound Eigen klanten", #"Rapportage Kwaliteitslijsten", #"Rapportage Outbound"}),
#"Namen van kolommen gewijzigd" = Table.RenameColumns(#"Rapportages toegevoegd", {{"KPL Rayonnummer", "KPL oud"}}),
#"Rijen gefilterd" = Table.SelectRows(#"Namen van kolommen gewijzigd", each ([#"Resultcode omschr."] = "Adviseur belt voor afspraak" or [#"Resultcode omschr."] = "Afspraak definitief - kantoorbezoek" or [#"Resultcode omschr."] = "Afspraak definitief - thuisbezoek" or [#"Resultcode omschr."] = "Doorgeschakeld naar FN voor afspraak" or [#"Resultcode omschr."] = "Reeds afspraak definitief - kantoorbezoek" or [#"Resultcode omschr."] = "Reeds afspraak definitief - thuisbezoek" or [#"Resultcode omschr."] = "Reeds belt voor afspraak" or [#"Resultcode omschr."] = "Reeds doorgeschakeld naar FN voor afspraak")),
#"Renamed Columns" = Table.RenameColumns(#"Rijen gefilterd", {{"KPL oud", "Rayonnummer"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each true),
#"Changed column type" = Table.TransformColumnTypes(#"Filtered Rows", {{"Beldatum", type date}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed column type", each Date.IsInCurrentMonth([Beldatum])),
#"Added Custom" = Table.AddColumn(#"Filtered Rows1", "geocoding", each ([Straatnaam]&""&[Huisnummer]&","&[Postcode]&","&[Woonplaats]&",NL")),
#"Invoked Custom Function" = Table.AddColumn(#"Added Custom", "geocode", each Geocoding([geocoding])),
#"Expanded geocode" = Table.ExpandTableColumn(#"Invoked Custom Function", "geocode", {"lat", "lng"}, {"lat", "lng"}),
#"Transform columns" = Table.TransformColumnTypes(#"Expanded geocode", {{"geocoding", type text}, {"lat", type number}, {"lng", type number}}),
#"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"geocoding", null}, {"lat", null}, {"lng", null}})
in
#"Replace errors"

Kind regards,

Bram Dekker

[Ruben-C1]

However when I deactivate the "enable staging", it works but i cant import it on the power bi desktop

[Anonymous · Accepted solution]

HI @Anonymous ,

I think this should related to your custom functions, it contains Record.ToTable functions.
If your records not have same format as cached in query steps, it will return error due to deal with not matched table fields.
Regards,

Xiaoxin Sheng

[Ruben-C1]

Hello I am having the same problem on dataflow gen 2 but I am not using a related.table as a merged query

I attach the steps i am using

et

  Source = Cristhian,

  #"Appended query" = Table.Combine({Source, Jonathan, Roxy, Patty, Editor}),

  #"Replaced errors" = Table.ReplaceErrorValues(#"Appended query", {{"Forecast premium", 0}}),

#"Replaced Value4" = Table.ReplaceValue(#"Replaced errors","Pre bind","Deep underwriter",Replacer.ReplaceText,{"Lead Status"}),

    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value4",{"Column1"}),

    #"Trimmed Text" = Table.TransformColumns(Table.TransformColumnTypes(#"Removed Columns", {{"Finance share", type text}}, "en-US"),{{"Finance share", Text.Trim, type text}}),

    #"Changed Type" = Table.TransformColumnTypes(#"Trimmed Text",{{"Finance share", type number}}),

    #"Trimmed Text1" = Table.TransformColumns(#"Changed Type",{{"Client", Text.Trim, type text}}),

    #"Added Custom" = Table.AddColumn(#"Trimmed Text1", "mes", each Date.Month([Expected closing day])),

    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"mes", type text}}),

    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "3 letras", each [#"mes"]&"-"&Text.Start([Client],3)),

    #"Uppercased Text" = Table.TransformColumns(#"Added Custom1",{{"3 letras", Text.Upper, type text}, {"Client", Text.Upper, type text}}),

    #"Replaced Value5" = Table.ReplaceValue(#"Uppercased Text",null,"No",Replacer.ReplaceValue,{"Money collected"}),

    #"Added Index" = Table.AddIndexColumn(#"Replaced Value5", "Index", 0, 1, Int64.Type),

    #"Sorted Rows" = Table.Sort(#"Added Index",{{"Expected closing day", Order.Ascending}}),

    #"Duplicated Column" = Table.DuplicateColumn(#"Sorted Rows", "Customer ID", "Customer ID - Copy"),

    #"Changed Type2" = Table.TransformColumnTypes(#"Duplicated Column",{{"Customer ID - Copy", type text}, {"Index", type text}}),

    #"Added Custom2" = Table.AddColumn(#"Changed Type2", "ID pre bind finance", each [#"Customer ID - Copy"]&"-"&[Index]),

    #"Duplicated Column1" = Table.DuplicateColumn(#"Added Custom2", "Index", "Index - Copy"),

    #"Changed Type3" = Table.TransformColumnTypes(#"Duplicated Column1",{{"Index - Copy", type text}, {"Expected closing day", type date}}),

    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type3", {"Forecast premium", "GI", "Finance share"}),

    #"Removed Errors1" = Table.RemoveRowsWithErrors(#"Removed Errors", {"Broker fee", "Expected closing day", "Effective date", "Health", "Lead Status", "Money collected", "Pre bind amount", "To do list", "Code", "mes", "3 letras", "Index", "Customer ID - Copy", "ID pre bind finance", "Index - Copy"}),

    #"Trimmed Text2" = Table.TransformColumns(#"Removed Errors1",{{"Index - Copy", Text.Trim, type text}, {"ID pre bind finance", Text.Trim, type text}, {"Customer ID - Copy", Text.Trim, type text}, {"Index", Text.Trim, type text}, {"3 letras", Text.Trim, type text}}),

    #"Duplicated Column2" = Table.DuplicateColumn(#"Trimmed Text2", "Index - Copy", "Index - Copy - Copy"),

    #"Changed Type4" = Table.TransformColumnTypes(#"Duplicated Column2",{{"Index - Copy - Copy", Int64.Type}}),

    #"Renamed Columns" = Table.RenameColumns(#"Changed Type4",{{"Index - Copy - Copy", "Index number"}}),

    #"Replaced Errors2" = Table.ReplaceErrorValues(#"Renamed Columns", {{"Lead date", null}}),

  #"Replaced errors 1" = Table.ReplaceErrorValues(#"Replaced Errors2", {{"Forecast premium", null}, {"Finance share", null}, {"Broker fee", null}, {"commission", null}, {"Expected closing day", null}}),

  #"Replaced errors 2" = Table.ReplaceErrorValues(#"Replaced errors 1", {{"Lead date", null}}),

  #"Renamed columns 1" = Table.RenameColumns(#"Replaced errors 2", {{"Customer ID", "Customer ID"}, {"ID pre bind finance", "pre bind finance"}, {"Customer ID - Copy", "Customer copy"}}),

  #"Removed columns 1" = Table.RemoveColumns(#"Renamed columns 1", {"Source.Name"}),

  #"Reordered columns" = Table.ReorderColumns(#"Removed columns 1", {"Customer ID", "New business/Renewals", "Lead date", "Name", "Client", "Forecast premium", "Finance share", "Broker fee", "commission", "Expected closing day", "Effective date", "Health", "Lead Status", "Money collected", "Pre bind amount", "To do list", "Code", "Column2", "Column3", "Column4", "GI", "Commission", "mes", "3 letras", "Index", "Customer copy", "pre bind finance", "Index - Copy", "Index number"}),

  #"Renamed columns 2" = Table.RenameColumns(#"Reordered columns", {{"Index - Copy", "IND"}, {"Index number", "IND COPY"}}),

  #"Added custom 1" = Table.AddColumn(#"Renamed columns 2", "ID", each "daily report"),

  #"Reordered columns 1" = Table.ReorderColumns(#"Added custom 1", {"ID", "Customer ID", "New business/Renewals", "Lead date", "Name", "Client", "Forecast premium", "Finance share", "Broker fee", "commission", "Expected closing day", "Effective date", "Health", "Lead Status", "Money collected", "Pre bind amount", "To do list", "Code", "Column2", "Column3", "Column4", "GI", "Commission", "mes", "3 letras", "Index", "Customer copy", "pre bind finance", "IND", "IND COPY"}),

  #"Renamed columns 3" = Table.RenameColumns(#"Reordered columns 1", {{"Customer ID", "Customer AM"}}),

  #"Reordered columns 2" = Table.ReorderColumns(#"Renamed columns 3", {"IND", "ID", "Customer AM", "New business/Renewals", "Lead date", "Name", "Client", "Forecast premium", "Finance share", "Broker fee", "commission", "Expected closing day", "Effective date", "Health", "Lead Status", "Money collected", "Pre bind amount", "To do list", "Code", "Column2", "Column3", "Column4", "GI", "Commission", "mes", "3 letras", "Index", "Customer copy", "pre bind finance", "IND COPY"}),

  #"Renamed columns 4" = Table.RenameColumns(#"Reordered columns 2", {{"ID", "source"}, {"IND", "ID"}}),

  #"Removed duplicates" = Table.Distinct(#"Renamed columns 4", {"ID"})

in

    #"Removed duplicates"

[Anonymous]

Hi Xiaoxin,

Thanks for the reply, I will mark it as a solution.
The next time this error comes up I will empty my browswers cache first and then try again.

Cheers,
Bram

[Anonymous]

I can't edit my original post but I was able to save once when copy pastig new code into the advanced editor. Then changed something in the query by removing a step, got the error again.