cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gwendalidot
Frequent Visitor

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
ChrisHaas Solution Sage
Solution Sage

Re: I can not refresh the data in Power Bi Desktop.

@Gwendalidot, 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

v-piga-msft Resident Rockstar
Resident Rockstar

Re: I can not refresh the data in Power Bi Desktop.

Hi @Gwendalidot ,

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.
v-piga-msft Resident Rockstar
Resident Rockstar

Re: I can not refresh the data in Power Bi Desktop.

Hi @Gwendalidot ,

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.
Gwendalidot
Frequent Visitor

Re: I can not refresh the data in Power Bi Desktop.

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

Gwendalidot
Frequent Visitor

Re: I can not refresh the data in Power Bi Desktop.

Hi,

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

Gwendalidot
Frequent Visitor

Re: I can not refresh the data in Power Bi Desktop.

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

Re: I can not refresh the data in Power Bi Desktop.

hi @Gwendalidot 

 

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

 

Gwendalidot
Frequent Visitor

Re: I can not refresh the data in Power Bi Desktop.

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

 

Gwendalidot
Frequent Visitor

Re: I can not refresh the data in Power Bi Desktop.

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é"

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors