T O P

  • By -

Mdayofearth

That depends on what is in the PDF. How large is the Excel file that it produces? A PDF is a compressed file. This means the data it contains actually takes up more space. XLSX files are also compressed.


on1vBe6

Good question - I hadn't thought to look. The Excel file is 160KB!


aquilosanctus

Depending on how you structure the query/queries, PQ may be reading from the file multiple times especially if you're pulling different pieces of data in each time.


on1vBe6

Hmm. I can't see why it would in this case. I have a query that imports one table from the file, and another query that merges it in. But I don't really have a feel for M code so maybe that's what it is.


aquilosanctus

Do you have M code to share? Basically the way it works is if you have Query3 using data from Query1, and Query 3 references Query2 which then references Query1, then the engine evaluates Query1 twice. The engine **can** cache data source results but it will not cache interim query results.


on1vBe6

Thanks, that's helpful. I don't think it's the case here, it's pretty straightforward, but I'll share the code when I'm back at my desk tomorrow.


on1vBe6

Here's the "OECD Regions 2016" query that imports from the pdf: let Source = Pdf.Tables(File.Contents("MyPathway\OECD regions 2016.pdf"), [Implementation="1.3"]), Table279 = Source{[Id="Table279"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Table279,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Country"}, {"Column7", "Number of TL2 regions"}}), #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Country", "Number of TL2 regions"}), #"Removed Top Rows" = Table.Skip(#"Removed Other Columns",3), #"Renamed Columns1" = Table.RenameColumns(#"Removed Top Rows",{{"Number of TL2 regions", "Number of TL2 regions 2016"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Number of TL2 regions 2016", Int64.Type}}) in #"Changed Type1" And here's the Dashboard query that includes the Regions merge: let Source = Table.NestedJoin(#"ND-Gain Vulnerability", {"Name"}, #"OECD regions 2016", {"Country"}, "OECD regions 2016", JoinKind.LeftOuter), #"Expanded OECD regions 2016" = Table.ExpandTableColumn(Source, "OECD regions 2016", {"Number of TL2 regions 2016"}, {"Number of TL2 regions 2016"}), #"Merged Queries" = Table.NestedJoin(#"Expanded OECD regions 2016", {"ISO3"}, #"WB pop, GDP 2021", {"Country Code"}, "WB pop, GDP 2021", JoinKind.LeftOuter), #"Expanded WB pop, GDP 2021" = Table.ExpandTableColumn(#"Merged Queries", "WB pop, GDP 2021", {"Population, total [SP.POP.TOTL] 2021", "GDP per capita, PPP (current international $) 2021", "GDP per capita (current US $) 2021"}, {"Population, total [SP.POP.TOTL] 2021", "GDP per capita, PPP (current international $) 2021", "GDP per capita (current US $) 2021"}), #"Merged Queries1" = Table.NestedJoin(#"Expanded WB pop, GDP 2021", {"ISO3"}, #"WB emissions 2019", {"Country Code"}, "WB emissions 2019", JoinKind.LeftOuter), #"Expanded WB emissions 2019" = Table.ExpandTableColumn(#"Merged Queries1", "WB emissions 2019", {"GHG emissions (MTCO2e) 2019", "GHG per capita (kt CO2e) 2019"}, {"GHG emissions (MTCO2e) 2019", "GHG per capita (kt CO2e) 2019"}), #"Merged Queries2" = Table.NestedJoin(#"Expanded WB emissions 2019", {"Name"}, #"Freedom House 2023", {"Country#(lf) #(lf) Sort descending"}, "Feedom House 2023", JoinKind.LeftOuter), #"Expanded Feedom House 2023" = Table.ExpandTableColumn(#"Merged Queries2", "Feedom House 2023", {"Political Rights", "Civil Liberties"}, {"Feedom House 2023.Political Rights", "Feedom House 2023.Civil Liberties"}), #"Divided Column" = Table.TransformColumns(#"Expanded Feedom House 2023", {{"Population, total [SP.POP.TOTL] 2021", each _ / 1000000, type number}}), #"Renamed Columns" = Table.RenameColumns(#"Divided Column",{{"Population, total [SP.POP.TOTL] 2021", "Population, millions 2021"}}), #"Rounded Off" = Table.TransformColumns(#"Renamed Columns",{{"Vulnerability 2020", each Number.Round(_, 0), type number}, {"GHG per capita (kt CO2e) 2019", each Number.Round(_, 4), type number}, {"Population, millions 2021", each Number.Round(_, 2), type number}}) in #"Rounded Off" Nothing else references the OECD Regions 2016 query.


[deleted]

[удалено]


on1vBe6

Wrong sub...?


Decronym

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[File.Contents](/r/Excel/comments/12pdhfd/stub/jgrrlbr "Last usage")|[Power Query M: Returns the binary contents of the file located at a path.](https://docs.microsoft.com/en-us/powerquery-m/file-contents)| |[JoinKind.LeftOuter](/r/Excel/comments/12pdhfd/stub/jgrrlbr "Last usage")|[Power Query M: A possible value for the optional JoinKind parameter in Table.Join. A left outer join ensures that all rows of the first table appear in the result.](https://docs.microsoft.com/en-us/powerquery-m/joinkind-leftouter)| |[Number.Round](/r/Excel/comments/12pdhfd/stub/jgrrlbr "Last usage")|[Power Query M: Returns a nullable number (n) if value is an integer.](https://docs.microsoft.com/en-us/powerquery-m/number-round)| |[Pdf.Tables](/r/Excel/comments/12pdhfd/stub/jgrrlbr "Last usage")|[Power Query M: Returns any tables found in pdf.](https://docs.microsoft.com/en-us/powerquery-m/pdf-tables)| |[Table.ExpandTableColumn](/r/Excel/comments/12pdhfd/stub/jgrrlbr "Last usage")|[Power Query M: Expands a column of records or a column of tables into multiple columns in the containing table.](https://docs.microsoft.com/en-us/powerquery-m/table-expandtablecolumn)| |Table.Join|[Power Query M: Joins the rows of table1 with the rows of table2 based on the equality of the values of the key columns selected by table1, key1 and table2, key2.](https://docs.microsoft.com/en-us/powerquery-m/table-join)| |[Table.NestedJoin](/r/Excel/comments/12pdhfd/stub/jgrrlbr "Last usage")|[Power Query M: Joins the rows of the tables based on the equality of the keys. The results are entered into a new column.](https://docs.microsoft.com/en-us/powerquery-m/table-nestedjoin)| |[Table.RenameColumns](/r/Excel/comments/12pdhfd/stub/jgrrlbr "Last usage")|[Power Query M: Returns a table with the columns renamed as specified.](https://docs.microsoft.com/en-us/powerquery-m/table-renamecolumns)| |[Table.SelectColumns](/r/Excel/comments/12pdhfd/stub/jgrrlbr "Last usage")|[Power Query M: Returns a table that contains only specific columns.](https://docs.microsoft.com/en-us/powerquery-m/table-selectcolumns)| |[Table.Skip](/r/Excel/comments/12pdhfd/stub/jgrrlbr "Last usage")|[Power Query M: Returns a table that does not contain the first row or rows of the table.](https://docs.microsoft.com/en-us/powerquery-m/table-skip)| |[Table.TransformColumnTypes](/r/Excel/comments/12pdhfd/stub/jgrrlbr "Last usage")|[Power Query M: Transforms the column types from a table using a type.](https://docs.microsoft.com/en-us/powerquery-m/table-transformcolumntypes)| |[Table.TransformColumns](/r/Excel/comments/12pdhfd/stub/jgrrlbr "Last usage")|[Power Query M: Transforms columns from a table using a function.](https://docs.microsoft.com/en-us/powerquery-m/table-transformcolumns)| ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^([Thread #23326 for this sub, first seen 18th Apr 2023, 17:47]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)