Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

I can not refresh the data in Power Bi Desktop.

Help. 

I can not refresh the data in Power Bi Desktop. 

Here are two screen shot. One of my Excel database. One of PowerBi Desktop with the error message. 

It says: "We did not find the column" Mail "of the table".

But, as it can be seen on the screen shot there no 

There is no "Mail" column to fill in Power Bi. But rather "Current Email". And as we see on the Excel table of the data it does appear a column "Current Email".

What can I do to refresh my PowerBi ?

 

Capture.PNGCapture2.PNG

11 REPLIES 11
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Anonymous ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Anonymous ,

For your issue, I think, you could go to Edit Queries and check the Change type step. 

If you find the column name "Mail", please change it to be "Current Emai".

If you still need help, please share the screenshot of your M query so that we could help further on it. Do mask the sensitive data before sharing.

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi, Capture5.PNGI am trying to update my data. The new table titled "Remediation" that I get after loading seems correct. However, the old table remains displayed. I have error message: your queries contain pending changes. When I click to apply the changes I get the error message: the key doesn't match any table

Anonymous
Not applicable

Hi,

What do you call the Queries Editor ? Is this the empty field to capture searches, right at the top? 

Anonymous
Not applicable

What do you call the Editor Queris ? Is this the empty field to capture searches, right at the top? 

Anonymous
Not applicable

@Anonymous, Please copy and paste the M code for the table here.

 

You can right click the table name in Query Editor and click "Advanced Editor".  Copy and paste that here, and we'll have a look.

 

~ Chris

Anonymous
Not applicable

Hi Chris
PBI exports the data from three tables : RH, eDash, and Lotus
This is the code from the RH table get with "Advanced Editor"
let
    Source = Excel.Workbook(File.Contents("C:\Users\nmatipa\Desktop\Remédiation\RH.xlsx"), null, true),
    RH_Sheet = Source{[Item="RH",Kind="Sheet"]}[Data],
    #"En-têtes promus" = Table.PromoteHeaders(RH_Sheet, [PromoteAllScalars=true]),
    #"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"Code Conso", Int64.Type}, {"Société", Int64.Type}, {"Société_1", type text}, {"Etablissement", Int64.Type}, {"Etablissement_2", type text}, {"Matricule", Int64.Type}, {"Qualité", type text}, {"* Nom de naissance", type text}, {"* Nom usuel", type text}, {"Prénom", type text}, {"Date d'entrée", type date}, {"Date de sortie adm", type date}, {"Service", type text}, {"Service_3", type text}, {"Section", type text}, {"Section_4", type text}, {"Sous-section", type text}, {"Sous-section_5", type text}, {"Service PS", type text}, {"Service PS_6", type text}, {"* Lieu de travail", type text}, {"Adresse mail", type text}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié", "NomPrénom", each [#"* Nom usuel"]&[Prénom]),
    #"Texte en minuscules" = Table.TransformColumns(#"Personnalisée ajoutée",{{"NomPrénom", Text.Lower, type text}}),
    #"Valeur remplacée" = Table.ReplaceValue(#"Texte en minuscules","ç","c",Replacer.ReplaceText,{"NomPrénom"}),
    #"Valeur remplacée1" = Table.ReplaceValue(#"Valeur remplacée","é","e",Replacer.ReplaceText,{"NomPrénom"}),
    #"Valeur remplacée2" = Table.ReplaceValue(#"Valeur remplacée1","ï","i",Replacer.ReplaceText,{"NomPrénom"}),
    #"Valeur remplacée3" = Table.ReplaceValue(#"Valeur remplacée2","ë","e",Replacer.ReplaceText,{"NomPrénom"}),
    #"Valeur remplacée4" = Table.ReplaceValue(#"Valeur remplacée3","-","",Replacer.ReplaceText,{"NomPrénom"}),
    #"Valeur remplacée5" = Table.ReplaceValue(#"Valeur remplacée4"," ","",Replacer.ReplaceText,{"NomPrénom"}),
    #"Texte en majuscules" = Table.TransformColumns(#"Valeur remplacée5",{{"NomPrénom", Text.Upper, type text}}),
    #"Espaces supprimés" = Table.TransformColumns(#"Texte en majuscules",{{"NomPrénom", Text.Trim, type text}}),
    #"Texte nettoyé" = Table.TransformColumns(#"Espaces supprimés",{{"NomPrénom", Text.Clean, type text}})
in
    #"Texte nettoyé"
 
This is the code from the eDash Table now, get with "Advanced Editor"
let
    Source = Csv.Document(File.Contents("C:\Users\nmatipa\Desktop\Remédiation\eDash.csv"),[Delimiter=",", Columns=16, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"En-têtes promus" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"""mail cible""", type text}, {"eN2O365_MigrationSlot", type text}, {"MailDomain", type text}, {"Erreurs", type text}, {"Prénom", type text}, {"Nom", type text}, {"mail actuel", type text}, {"Société / Pôle", type text}, {"O365_IsLicensed", type logical}, {"O365_Licenses", type text}, {"O365_RecipientType", type text}, {"O365_RecipientTypeDetails", type text}, {"Profil", type text}, {"Population", type text}, {"CodeConso", Int64.Type}, {"TypeCompte", type text}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié", "NomPrénom", each [Nom]&[Prénom]),
    #"Texte en minuscules" = Table.TransformColumns(#"Personnalisée ajoutée",{{"NomPrénom", Text.Lower, type text}}),
    #"Valeur remplacée" = Table.ReplaceValue(#"Texte en minuscules","é","e",Replacer.ReplaceText,{"NomPrénom"}),
    #"Valeur remplacée1" = Table.ReplaceValue(#"Valeur remplacée","è","e",Replacer.ReplaceText,{"NomPrénom"}),
    #"Valeur remplacée2" = Table.ReplaceValue(#"Valeur remplacée1","ç","c",Replacer.ReplaceText,{"NomPrénom"}),
    #"Valeur remplacée3" = Table.ReplaceValue(#"Valeur remplacée2","ï","i",Replacer.ReplaceText,{"NomPrénom"}),
    #"Valeur remplacée4" = Table.ReplaceValue(#"Valeur remplacée3","ë","e",Replacer.ReplaceText,{"NomPrénom"}),
    #"Valeur remplacée5" = Table.ReplaceValue(#"Valeur remplacée4","-"," ",Replacer.ReplaceText,{"NomPrénom"}),
    #"Valeur remplacée6" = Table.ReplaceValue(#"Valeur remplacée5"," ","",Replacer.ReplaceText,{"NomPrénom"}),
    #"Texte en majuscules" = Table.TransformColumns(#"Valeur remplacée6",{{"NomPrénom", Text.Upper, type text}}),
    #"Espaces supprimés" = Table.TransformColumns(#"Texte en majuscules",{{"NomPrénom", Text.Trim, type text}}),
    #"Texte nettoyé" = Table.TransformColumns(#"Espaces supprimés",{{"NomPrénom", Text.Clean, type text}})
in
    #"Texte nettoyé"
 
And this is the code from the Lotus table :
 
let
    Source = Csv.Document(File.Contents("C:\Users\nmatipa\Desktop\Remédiation\Lotus.csv"),[Delimiter=",", Columns=33, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"En-têtes promus" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"""mail cible""", type text}, {"eN2O365_MigrationSlot", type text}, {"MailDomain", type text}, {"Erreurs", type text}, {"Prénom", type text}, {"Nom", type text}, {"mail actuel", type text}, {"Société / Pôle", type text}, {"O365_IsLicensed", type logical}, {"O365_Licenses", type text}, {"O365_RecipientType", type text}, {"O365_RecipientTypeDetails", type text}, {"Profil", type text}, {"Population", type text}, {"CodeConso", Int64.Type}, {"TypeCompte", type text}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié" , "NomPrénom", each [LastName]&[FirstName]),
    #"Texte en minuscules" = Table.TransformColumns(#"Personnalisée ajoutée",{{"NomPrénom", Text.Lower, type text}}),
    #"Valeur remplacée" = Table.ReplaceValue(#"Texte en minuscules","é","e",Replacer.ReplaceText,{"NomPrénom"}),
    #"Valeur remplacée1" = Table.ReplaceValue(#"Valeur remplacée","è","e",Replacer.ReplaceText,{"NomPrénom"}),
    #"Valeur remplacée2" = Table.ReplaceValue(#"Valeur remplacée1","ï","i",Replacer.ReplaceText,{"NomPrénom"}),
    #"Valeur remplacée3" = Table.ReplaceValue(#"Valeur remplacée2","ë","e",Replacer.ReplaceText,{"NomPrénom"}),
    #"Valeur remplacée4" = Table.ReplaceValue(#"Valeur remplacée3","ç","c",Replacer.ReplaceText,{"NomPrénom"}),
    #"Valeur remplacée5" = Table.ReplaceValue(#"Valeur remplacée4","-"," ",Replacer.ReplaceText,{"NomPrénom"}),
    #"Valeur remplacée6" = Table.ReplaceValue(#"Valeur remplacée5"," ","",Replacer.ReplaceText,{"NomPrénom"}),
    #"Texte en majuscules" = Table.TransformColumns(#"Valeur remplacée6",{{"NomPrénom", Text.Upper, type text}}),
    #"Espaces supprimés" = Table.TransformColumns(#"Texte en majuscules",{{"NomPrénom", Text.Trim, type text}}),
    #"Texte nettoyé" = Table.TransformColumns(#"Espaces supprimés",{{"NomPrénom", Text.Clean, type text}})
in
    #"Texte nettoyé"
Anonymous
Not applicable

Hello, 

As you can see on the screen shot. The line 2 (Nom usuel / Prenom / Email actuel) should replace the line 1 (Column1 / Column 2 / Column 3). CapturePBI2.PNG This seems to be the cause. PowerBi can't update the data because it doesn't properly recognize the header line of the source file.

Anonymous
Not applicable

hi @Anonymous 

 

Follow these steps:

1. After retriving excel data, Go Editqueries --> applied steps

2. applied steps you have 3 steps are showing like (Source, Promoted Headers, Change type)

3. remove the change type step

4. close and apply

5. refresh your data set

 

regards,

Naveen

 

Anonymous
Not applicable

Hi, Unfortunately, it has not yet worked. But by doing several tests, I understand the problem better. As appears on the attached screenshots the problem is in importing a file needed to make the update. To do the update I import three files actually that are compiled to give a fourth file that needs to be displayed. The problem is only the import of one file out of all three. I show here two screenshots. One of a file that poses no problem. The other one in the file that blocks. In fact, all file content is blocked in the content of the first column instead of being spread across multiple columns. The first screen copy is correct. While on the second we see that PowerBi did not select the first line of the source file to fill the titles of each column.Capture4.PNGCapture3.PNG

Anonymous
Not applicable

CapturePBI3.PNGHi, Thanks a lot,

By "remove the change type step" what do you mean ? remove le line 3 completely ? from "Change type" to {" ? 

Or juste remove "Change type" = Table.transformColumns ?  

If you look my screen shotCapturePBI3.PNG

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.