Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
i face an well known issue, so i looked around, and i parametered my excel power query like here,
the trouble is that it works fine on my laptop, but not on my teammates ' one.
i don't understand why....
here is the code of my queries
first, i pick up the file in an excel cell.
let
Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
#"Type modifié" = Table.TransformColumnTypes(Source,{{"Emplacement Fichier", type text}}),
#"Emplacement Fichier" = #"Type modifié"{0}[Emplacement Fichier]
in
#"Emplacement Fichier"
then i make a first reference query
let
Source = Excel.Workbook(File.Contents(SourceFichier))[Data],
#"Conserver les premiers éléments" = List.FirstN(Source,1),
#"Converti en table" = Table.FromList(#"Conserver les premiers éléments", Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converti en table"
and last, the final one.
let
Source = Requête1,
//#"Conserver les premiers éléments" = List.FirstN(Source,1),
//#"Converti en table" = Table.FromList(#"Conserver les premiers éléments", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Column1 développé" = Table.ExpandTableColumn(Source, "Column1", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"}, {"Column1.1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"}),
#"Colonnes renommées1" = Table.RenameColumns(#"Column1 développé",{{Table.ColumnNames(#"Column1 développé"){0}, "Colonne1"}, {"Column2", "Colonne2"}, {"Column3", "Colonne3"}, {"Column4", "Colonne4"}, {"Column5", "Colonne5"}, {"Column6", "Colonne6"}, {"Column7", "Colonne7"}, {"Column8", "Colonne8"}, {"Column9", "Colonne9"}, {"Column10", "Colonne10"}, {"Column11", "Colonne11"}, {"Column12", "Colonne12"}}),
#"Type modifié" = Table.TransformColumnTypes(#"Colonnes renommées1",{{"Colonne1", type text}, {"Colonne2", type text}, {"Colonne3", type text}, {"Colonne4", type text}, {"Colonne5", type any}, {"Colonne6", type text}, {"Colonne7", type any}, {"Colonne8", type any}, {"Colonne9", type any}, {"Colonne10", type any}, {"Colonne11", type any}, {"Colonne12", type any}}),
#"Rempli vers le bas" = Table.FillDown(#"Type modifié",{"Colonne1", "Colonne2", "Colonne3"}),
#"Texte inséré après le délimiteur" = Table.AddColumn(#"Rempli vers le bas", "Date", each Text.AfterDelimiter([Colonne3], " ", 3), type text),
#"Colonnes permutées" = Table.ReorderColumns(#"Texte inséré après le délimiteur",{"Colonne1", "Colonne2", "Colonne3", "Date", "Colonne4", "Colonne5", "Colonne6", "Colonne7", "Colonne8", "Colonne9", "Colonne10", "Colonne11", "Colonne12"}),
#"Premières lignes supprimées" = Table.Skip(#"Colonnes permutées",2),
#"Type modifié1" = Table.TransformColumnTypes(#"Premières lignes supprimées",{{"Date", type date}}),
#"Lignes filtrées" = Table.SelectRows(#"Type modifié1", each ([Colonne4] <> null)),
#"Colonnes supprimées" = Table.RemoveColumns(#"Lignes filtrées",{"Colonne1", "Colonne2", "Colonne3", "Colonne6", "Colonne7", "Colonne8", "Colonne9", "Colonne10", "Colonne11", "Colonne12"}),
#"Colonnes renommées" = Table.RenameColumns(#"Colonnes supprimées",{{"Colonne4", "Titre"}, {"Colonne5", "Nombre"}}),
#"Lignes filtrées1" = Table.SelectRows(#"Colonnes renommées", each ([Titre] <> "Total Abonnement" and [Titre] <> "Total Occasionnel")),
#"Type modifié2" = Table.TransformColumnTypes(#"Lignes filtrées1",{{"Nombre", Int64.Type}})
in
#"Type modifié2"
as i said, it's OK on my laptop, not on my teammates ' ones
any help would be welcome !
Solved! Go to Solution.
I didn't realize your title was the error. Power Query referencing other queries or steps is totally normal. I suspect you have a privacy issue. I cannot read that dialog box you posted.
The quick fix for Excel is to disable privacy settings. In Power Query, go to File, Options, and change to this setting:
The long fix is to get a cup of coffee and read this article on privacy settings and tinker with it. But I'll be honest - in Excel, I always disable it. In Power BI I am more careful about it since I use Power BI to connect to many many more different sources. Excel for me is usually Excel files, CSV files, or SQL Server, all on prem, so I'm not worried about privacy.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingWhat do you mean "doesn't work?"
You get an error?
You get a different result?
His computer crashes?
Please be specific. What you've explained is a bit like dropping your car off at the mechanic and saying "Well, when I drive on this road it makes one noise, but on another road it doesn't" and then expecting the machanic to figure it all out.
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi,
i thought the title was enough explicit.
so the queries stop at the first step.
with this message :
But as i explained, it works fine on my computer and failed with the above message on other computers.
i don't know what to say more, we have the same computer and excel versions
thanks
I didn't realize your title was the error. Power Query referencing other queries or steps is totally normal. I suspect you have a privacy issue. I cannot read that dialog box you posted.
The quick fix for Excel is to disable privacy settings. In Power Query, go to File, Options, and change to this setting:
The long fix is to get a cup of coffee and read this article on privacy settings and tinker with it. But I'll be honest - in Excel, I always disable it. In Power BI I am more careful about it since I use Power BI to connect to many many more different sources. Excel for me is usually Excel files, CSV files, or SQL Server, all on prem, so I'm not worried about privacy.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @edhans
thanks a lot, that was the reason !
i should have think, i had modified my privacy settings on my computer but had completly forgotten !
Sorry for the inconveniance
Not an inconvenience at all @GeekAlfPro . I was helping a client a few weeks ago and I couldn't figure out for the life of me why my queries were working and hers were not, then I remembered this setting. It is one of those things you set and then never again until you get a new computer and have to change the setting again. Glad you and your team are up and running!
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
52 | |
21 | |
12 | |
11 |