cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DanLund
Level: Powered On

Problem with Data integrating project, PQ works fine on Test environment but don't work in prod environment

Hi,

 

I have tried creating av Data integrating project in CDS on my prod environment. When the first error occurred, I created an test environment for tests. Now I have created a PowerQuery that works fine in the test environment, but when i copy/paste the PowerQuery to my prod environment, I get the error below.

 

Information about configuration. I connect through local data gateway with windows credentials. The target CDS is now reduced to an entity only with name and id(key).

 

The PowerQuery that works fine i a test environment.

let
Kilde = Json.Document(Web.Contents("https://[SharePoint_site]/_api/web/lists/GetByTitle('[ListName]')/items?$top=999999", [Headers=[Accept="application/json;odata=verbose"]])),
d = Kilde[d],
results = d[results],
#"Konvertert til tabell" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Utvidet Column2" = Table.ExpandRecordColumn(#"Konvertert til tabell", "Column1", {"Id", "Kategori", "Prosjekt_x0020_type", "Kort_x0020_beskrivelse_x0020_av_", "ProsjektlederId", "Assisterende_x0020_prosjektlederId", "Statistikk_x0020_ressursId", "Juridsk_x0020_ressursId", "Antatt_x0020_omsetning_x0020_p_x", "OFDP_x0020_M0", "OFDP_x0020_M1", "OFDP_x0020_M2", "OFDP_x0020_M3", "OFDP_x0020_M4", "OFDP_x0020_M5", "OFDP_x0020_M6", "OFDP_x0020_M7", "OFDP_x0020_M8", "OFDP_x0020_M9", "OFDP_x0020_M10", "SGFDP_x0020_M0", "SGFDP_x0020_M1", "SGFDP_x0020_M2", "SGFDP_x0020_M3", "SGFDP_x0020_M4", "SGFDP_x0020_M5", "SGFDP_x0020_M6", "SGFDP_x0020_M7", "SGFDP_x0020_M8", "SGFDP_x0020_M9", "SGFDP_x0020_M10", "FF_x0020_M0", "FF_x0020_M1", "FF_x0020_M2", "FF_x0020_M3", "FF_x0020_M4", "FF_x0020_M5", "FF_x0020_M6", "FF_x0020_M7", "FF_x0020_M8", "FF_x0020_M9", "FF_x0020_M10", "OFDP_x0020_B0", "OFDP_x0020_B1", "OFDP_x0020_B2", "OFDP_x0020_B3", "SGFDP_x0020_B0", "SGFDP_x0020_B1", "SGFDP_x0020_B2", "SGFDP_x0020_B3", "FF_x0020_B0", "FF_x0020_B1", "FF_x0020_B2", "FF_x0020_B3", "Title", "Avtale_x0020_utg_x00e5_r", "Navn_x0020_p_x00e5__x0020_valgte", "PGD_x0020_Helse_x0020_NordId", "PGD_x0020_Helse_x0020_Midt_x002dId", "PGD_x0020_Helse_x0020_S_x00f8_r_Id", "PGD_x0020_Helse_x0020_VestId", "Avtale_x0020_start", "Prosjekt_x0020_status", "Inng_x00e5_ende_x0020_BC", "Klager_x0020_tatt_x0020_til_x002", "Utg_x00e5_ende_x0020_BC", "Innkjopsgruppe", "OFDP_x0020_M11", "SGFDP_x0020_M11", "FF_x0020_M11", "Styringsgruppe", "Prosjektnavn", "Prolongeringstype", "Business_x0020_case_x0020_status", "Prosjektnummer", "ErstatterAvtalerId", "Avvikskoder", "AvtaleforvalterId", "Plan", "FOAMaksimal", "HF", "AvtaleType", "ID", "Modified", "Created", "AuthorId", "EditorId"}, {"Id", "Kategori", "Prosjekt_x0020_type", "Kort_x0020_beskrivelse_x0020_av_", "ProsjektlederId", "Assisterende_x0020_prosjektlederId", "Statistikk_x0020_ressursId", "Juridsk_x0020_ressursId", "Antatt_x0020_omsetning_x0020_p_x", "OFDP_x0020_M0", "OFDP_x0020_M1", "OFDP_x0020_M2", "OFDP_x0020_M3", "OFDP_x0020_M4", "OFDP_x0020_M5", "OFDP_x0020_M6", "OFDP_x0020_M7", "OFDP_x0020_M8", "OFDP_x0020_M9", "OFDP_x0020_M10", "SGFDP_x0020_M0", "SGFDP_x0020_M1", "SGFDP_x0020_M2", "SGFDP_x0020_M3", "SGFDP_x0020_M4", "SGFDP_x0020_M5", "SGFDP_x0020_M6", "SGFDP_x0020_M7", "SGFDP_x0020_M8", "SGFDP_x0020_M9", "SGFDP_x0020_M10", "FF_x0020_M0", "FF_x0020_M1", "FF_x0020_M2", "FF_x0020_M3", "FF_x0020_M4", "FF_x0020_M5", "FF_x0020_M6", "FF_x0020_M7", "FF_x0020_M8", "FF_x0020_M9", "FF_x0020_M10", "OFDP_x0020_B0", "OFDP_x0020_B1", "OFDP_x0020_B2", "OFDP_x0020_B3", "SGFDP_x0020_B0", "SGFDP_x0020_B1", "SGFDP_x0020_B2", "SGFDP_x0020_B3", "FF_x0020_B0", "FF_x0020_B1", "FF_x0020_B2", "FF_x0020_B3", "Title", "Avtale_x0020_utg_x00e5_r", "Navn_x0020_p_x00e5__x0020_valgte", "PGD_x0020_Helse_x0020_NordId", "PGD_x0020_Helse_x0020_Midt_x002dId", "PGD_x0020_Helse_x0020_S_x00f8_r_Id", "PGD_x0020_Helse_x0020_VestId", "Avtale_x0020_start", "Prosjekt_x0020_status", "Inng_x00e5_ende_x0020_BC", "Klager_x0020_tatt_x0020_til_x002", "Utg_x00e5_ende_x0020_BC", "Innkjopsgruppe", "OFDP_x0020_M11", "SGFDP_x0020_M11", "FF_x0020_M11", "Styringsgruppe", "Prosjektnavn", "Prolongeringstype", "Business_x0020_case_x0020_status", "Prosjektnummer", "ErstatterAvtalerId", "Avvikskoder", "AvtaleforvalterId", "Plan", "FOAMaksimal", "HF", "AvtaleType", "ID.1", "Modified", "Created", "AuthorId", "EditorId"}),
#"Utvidet HF" = Table.ExpandRecordColumn(#"Utvidet Column2", "HF", {"results"}, {"results"}),
#"Uttrukne verdier" = Table.TransformColumns(#"Utvidet HF", {"results", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Kolonner med nye navn" = Table.RenameColumns(#"Uttrukne verdier", {{"Prosjekt_x0020_type", "Prosjekt type"}, {"Kort_x0020_beskrivelse_x0020_av_", "Kort beskrivelse"}, {"Assisterende_x0020_prosjektlederId", "Assisterende prosjektlederId"}, {"Statistikk_x0020_ressursId", "Statistikk ressursId"}, {"Juridsk_x0020_ressursId", "Juridsk ressursId"}, {"Antatt_x0020_omsetning_x0020_p_x", "Antatt omsetning per år"}, {"OFDP_x0020_M0", "OFDP M0"}, {"OFDP_x0020_M1", "OFDP M1"}, {"OFDP_x0020_M2", "OFDP M2"}, {"OFDP_x0020_M3", "OFDP M3"}, {"OFDP_x0020_M4", "OFDP M4"}, {"OFDP_x0020_M5", "OFDP M5"}, {"OFDP_x0020_M6", "OFDP M6"}, {"OFDP_x0020_M7", "OFDP M7"}, {"OFDP_x0020_M8", "OFDP M8"}, {"OFDP_x0020_M9", "OFDP M9"}, {"OFDP_x0020_M10", "OFDP M10"}, {"OFDP_x0020_M11", "OFDP M11"}, {"OFDP_x0020_B0", "OFDP B0"}, {"OFDP_x0020_B1", "OFDP B1"}, {"OFDP_x0020_B2", "OFDP B2"}, {"OFDP_x0020_B3", "OFDP B3"}, {"SGFDP_x0020_M0", "SGFDP M0"}, {"SGFDP_x0020_M1", "SGFDP M1"}, {"SGFDP_x0020_M2", "SGFDP M2"}, {"SGFDP_x0020_M3", "SGFDP M3"}, {"SGFDP_x0020_M4", "SGFDP M4"}, {"SGFDP_x0020_M5", "SGFDP M5"}, {"SGFDP_x0020_M6", "SGFDP M6"}, {"SGFDP_x0020_M7", "SGFDP M7"}, {"SGFDP_x0020_M8", "SGFDP M8"}, {"SGFDP_x0020_M9", "SGFDP M9"}, {"SGFDP_x0020_M10", "SGFDP M10"}, {"SGFDP_x0020_M11", "SGFDP M11"}, {"SGFDP_x0020_B0", "SGFDP B0"}, {"SGFDP_x0020_B1", "SGFDP B1"}, {"SGFDP_x0020_B2", "SGFDP B2"}, {"SGFDP_x0020_B3", "SGFDP B3"}, {"FF_x0020_M0", "FF M0"}, {"FF_x0020_M1", "FF M1"}, {"FF_x0020_M2", "FF M2"}, {"FF_x0020_M3", "FF M3"}, {"FF_x0020_M4", "FF M4"}, {"FF_x0020_M5", "FF M5"}, {"FF_x0020_M6", "FF M6"}, {"FF_x0020_M7", "FF M7"}, {"FF_x0020_M8", "FF M8"}, {"FF_x0020_M9", "FF M9"}, {"FF_x0020_M10", "FF M10"}, {"FF_x0020_M11", "FF M11"}, {"FF_x0020_B0", "FF B0"}, {"FF_x0020_B1", "FF B1"}, {"FF_x0020_B2", "FF B2"}, {"FF_x0020_B3", "FF B3"}, {"Avtale_x0020_utg_x00e5_r", "Avtale utgår"}, {"Navn_x0020_p_x00e5__x0020_valgte", "Navn på valgte"}, {"PGD_x0020_Helse_x0020_NordId", "PGD Helse NordId"}, {"PGD_x0020_Helse_x0020_Midt_x002dId", "PGD Helse MidtNorgeId"}, {"PGD_x0020_Helse_x0020_S_x00f8_r_Id", "PGD Helse Sør_Id"}, {"PGD_x0020_Helse_x0020_VestId", "PGD Helse VestId"}, {"Avtale_x0020_start", "Avtale start"}, {"Prosjekt_x0020_status", "Prosjekt status"}, {"Inng_x00e5_ende_x0020_BC", "Inngående BC"}, {"Klager_x0020_tatt_x0020_til_x002", "Klager tatt til følge"}, {"Utg_x00e5_ende_x0020_BC", "Utgående BC"}, {"Business_x0020_case_x0020_status", "Business case status"}, {"results", "HF"}, {"Kategori", "Avdeling"}}),
Taksonomi = Table.AddColumn(#"Kolonner med nye navn", "Taksonomi", each Text.Start([Innkjopsgruppe], 10)),
#"Prefiks satt inn" = Table.AddColumn(Taksonomi, "ProsjektID", each "Nord-" & Text.From([Prosjektnummer]), type text),
#"Erstattet verdi" = Table.ReplaceValue(#"Prefiks satt inn", "Ad hoc 2018", "Ikke planlagt 2018", Replacer.ReplaceValue, {"Plan"}),
#"Erstattet verdi 1" = Table.ReplaceValue(#"Erstattet verdi", "Ad hoc 2019", "Ikke planlagt 2019", Replacer.ReplaceValue, {"Plan"})
in
#"Erstattet verdi 1"

 

Error i get:

Innlasting til Common Data Service mislyktes. Detaljer: [(PQOnline failure - The powerquery job failed with error: Challenge ChallengeType:Resource, ChallengeDataSource:{"Kind":"Web","Path":"https://[SP_site]/_api/web/lists/GetByTitle('ListName')/items?$top=999999","NormalizedPath":"https://[SP_site]/_api/web/lists/GetByTitle('[ListName]')/items","IsDefaultForKind":false})], One or more errors occurred.. (Jobb-ID: 6ccd596b-dd9d-4fe7-933d-fede2e8127b1)

 

 

Another problem i encounter, which is that Execution history fault when I try to open it. See error on attach image. (loosly translated the error message complain about "This prosjekt has assignment errors.", I did not get the same error in the test environment.)

 

Kind regards Dan

1 ACCEPTED SOLUTION

Accepted Solutions
DanLund
Level: Powered On

Re: Problem with Data integrating project, PQ works fine on Test environment but don't work in prod environment

Update on my issue.

 

I found inconsistency on datatypes in the mapping from source (PQ) to destination (CDS) entity. When i corrected these inconsistency I got one step closer to the finish line.

 

The datatype mappings in question was

String to OptionSet (Fix: Download OptionSet table in PQ and merge queries into source query with number value)

String to DateTime (Work in progress: Data integration expect US format but PQ serves european format)

 

Only a few rows/entries are actually imported to CDS. The dateformat was the problem here. By some reason CDS data integrasjon only accepts US formatted dateformats, but regular PQ date columns gets norwegian formatted dates. The following fixed this.

 

#"Endret kolonnetype" = Table.TransformColumnTypes(#"Utvidet OptionSetPlan", {{"OFDP M0", type datetimezone}, {"OFDP M6", type datetimezone}, {"OFDP M7", type datetimezone}, {"SGFDP M0", type datetimezone}, {"SGFDP M6", type datetimezone}, {"SGFDP M7", type datetimezone}, {"FF M0", type datetimezone}, {"FF M6", type datetimezone}, {"FF M7", type datetimezone}, {"Antatt omsetning per år", Int64.Type}}),
#"Beregnet dato" = Table.TransformColumns(#"Endret kolonnetype", {{"OFDP M0", each Date.ToText(DateTime.Date(_), "yyyy-MM-dd"), type date}, {"OFDP M6", each Date.ToText(DateTime.Date(_), "yyyy-MM-dd"), type date}, {"OFDP M7", each Date.ToText(DateTime.Date(_), "yyyy-MM-dd"), type date}, {"SGFDP M0", each Date.ToText(DateTime.Date(_), "yyyy-MM-dd"), type date}, {"SGFDP M6", each Date.ToText(DateTime.Date(_), "yyyy-MM-dd"), type date}, {"SGFDP M7", each Date.ToText(DateTime.Date(_), "yyyy-MM-dd"), type date}, {"FF M0", each Date.ToText(DateTime.Date(_), "yyyy-MM-dd"), type date}, {"FF M6", each Date.ToText(DateTime.Date(_), "yyyy-MM-dd"), type date}, {"FF M7", each Date.ToText(DateTime.Date(_), "yyyy-MM-dd"), type date}})

Hope this helps some one.

1 REPLY 1
DanLund
Level: Powered On

Re: Problem with Data integrating project, PQ works fine on Test environment but don't work in prod environment

Update on my issue.

 

I found inconsistency on datatypes in the mapping from source (PQ) to destination (CDS) entity. When i corrected these inconsistency I got one step closer to the finish line.

 

The datatype mappings in question was

String to OptionSet (Fix: Download OptionSet table in PQ and merge queries into source query with number value)

String to DateTime (Work in progress: Data integration expect US format but PQ serves european format)

 

Only a few rows/entries are actually imported to CDS. The dateformat was the problem here. By some reason CDS data integrasjon only accepts US formatted dateformats, but regular PQ date columns gets norwegian formatted dates. The following fixed this.

 

#"Endret kolonnetype" = Table.TransformColumnTypes(#"Utvidet OptionSetPlan", {{"OFDP M0", type datetimezone}, {"OFDP M6", type datetimezone}, {"OFDP M7", type datetimezone}, {"SGFDP M0", type datetimezone}, {"SGFDP M6", type datetimezone}, {"SGFDP M7", type datetimezone}, {"FF M0", type datetimezone}, {"FF M6", type datetimezone}, {"FF M7", type datetimezone}, {"Antatt omsetning per år", Int64.Type}}),
#"Beregnet dato" = Table.TransformColumns(#"Endret kolonnetype", {{"OFDP M0", each Date.ToText(DateTime.Date(_), "yyyy-MM-dd"), type date}, {"OFDP M6", each Date.ToText(DateTime.Date(_), "yyyy-MM-dd"), type date}, {"OFDP M7", each Date.ToText(DateTime.Date(_), "yyyy-MM-dd"), type date}, {"SGFDP M0", each Date.ToText(DateTime.Date(_), "yyyy-MM-dd"), type date}, {"SGFDP M6", each Date.ToText(DateTime.Date(_), "yyyy-MM-dd"), type date}, {"SGFDP M7", each Date.ToText(DateTime.Date(_), "yyyy-MM-dd"), type date}, {"FF M0", each Date.ToText(DateTime.Date(_), "yyyy-MM-dd"), type date}, {"FF M6", each Date.ToText(DateTime.Date(_), "yyyy-MM-dd"), type date}, {"FF M7", each Date.ToText(DateTime.Date(_), "yyyy-MM-dd"), type date}})

Hope this helps some one.