cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GeekAlfPro
Advocate I
Advocate I

POWER QUERY references other queries or steps

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 !

1 ACCEPTED 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:

edhans_0-1614105219891.png

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

5 REPLIES 5
edhans
Super User III
Super User III

What 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Hi,

 

i thought the title was enough explicit.

so the queries stop at the first step.

with this message : 

 

Capture.PNG

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:

edhans_0-1614105219891.png

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Hi @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!



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors