I am often asked which is the simplest way in PQ to change format of say $1,234,557 to $1.23M (with negatives in RED in brackets) Dead easy in BI, some simple VBA in Excel but the FORMAT in PQ I find clunky.. Might make a good topic!! love your work and you are getting popular down under!!
Power Query can handle more than 1 million rows. It cannot load them to the sheet because Excel doe snot have enough rows. But Power Query can store them and load them to a PivotTable and/or model.
Thank you, but I have a question, I have already loaded a query into a table, but now I want to change that specific query to "connection only", do we have any such option available.
Sure. If you right-click the query on the 'Queries and Connections' pane on the right, and click 'Load To'. It will ask you if you want to remove the Table, for which you can click Ok.
@@Computergaga, I've got a Medicare Cost Report at the hospital where I am that comes out as a .pdf only. Converting it to Excel breaks it into tables and to separate pages. I can get it back together, just seeing if there was a way to skip that step. Trying to create a tool for others to use after I'm gone. Thanks for the response. Your video had me holding my breath. Timely as always. This one just wasn't cut out for me. But informative and helpful as always. Cheers!
@@Computergaga, it comes as one .pdf currently. That is the only output that it comes. I am converting it to Excel using Power Query. It is seeing the file as multiple tables and outputs them to multiple tabs. I'm doing this as the report is used for just about every other financial report. Before me, they were pasting screenshots into Excel and putting the data in cells next to this. I'm working on a clean report (doesn't reveal the hospital) to use for documentation purposes. I'll share that with you when that's completed, if you'd like.
This is important because on many occasions we don't want to load the queries in the spreadsheets. It is very practical what you have told Alan!
Thanks Iván.
Alan, could you do a challenge and a tutorial on how to do the ranked-choice voting (instant runoff voting) on Excel and on Google Sheets?
I am often asked which is the simplest way in PQ to change format of say $1,234,557 to $1.23M (with negatives in RED in brackets) Dead easy in BI, some simple VBA in Excel but the FORMAT in PQ I find clunky.. Might make a good topic!! love your work and you are getting popular down under!!
Use a custom format on the cells:
$0.00,, "M";[Red]$0.00,, "M"
(or $0.00,,, "B").
MAAAAAAAAAAAAAASTER!!! thanks a lot!!
Nom worries 👍
Hi there!
Can you do a video why power query stopped loading data after a 1million rows? Is it due to laptop specs?
Power Query can handle more than 1 million rows. It cannot load them to the sheet because Excel doe snot have enough rows. But Power Query can store them and load them to a PivotTable and/or model.
Thank you, but I have a question, I have already loaded a query into a table, but now I want to change that specific query to "connection only", do we have any such option available.
Sure. If you right-click the query on the 'Queries and Connections' pane on the right, and click 'Load To'. It will ask you if you want to remove the Table, for which you can click Ok.
@@Computergaga thank you Bhai, i will try it
@@Computergaga Thank you So Much! I had been searching for it for a long time. I appreciate your time and support.😀
Anyway to get them to load on one page?
I don't believe this is possible without changing the Load To option individually for each query.
@@Computergaga, I've got a Medicare Cost Report at the hospital where I am that comes out as a .pdf only. Converting it to Excel breaks it into tables and to separate pages. I can get it back together, just seeing if there was a way to skip that step. Trying to create a tool for others to use after I'm gone. Thanks for the response. Your video had me holding my breath. Timely as always. This one just wasn't cut out for me. But informative and helpful as always. Cheers!
@@michaelt312 what process are you currently using. You can import and append all pages of a PDF together quick. This process sounds refreshable.
@@Computergaga, it comes as one .pdf currently. That is the only output that it comes. I am converting it to Excel using Power Query. It is seeing the file as multiple tables and outputs them to multiple tabs. I'm doing this as the report is used for just about every other financial report. Before me, they were pasting screenshots into Excel and putting the data in cells next to this. I'm working on a clean report (doesn't reveal the hospital) to use for documentation purposes. I'll share that with you when that's completed, if you'd like.