Power Query - Avoid "Helper Queries" (+10 Cool Tricks) 

Thanks for taking the time to watch. Glad it helped. 👍👍

I like this video ❤❤❤❤

Fantastic

Thank you so much for your VERY kind and supportive words. Thanks for watching!!!

So glad it will help. I prefer this method (in most cases.) Thanks for watching!!

@@bcti-bcti At work, I was asked to automate the updating and consolidation of spec files when part numbers change. Prior process was to assign someone to manually go through hundreds of files, with tens of thousands of rows and MANUALLY copy/paste/Q.C. the data. Whenever specs were updated (several times per year), this error prone process would take a full time user about a month. Using PQ, I got that down to about 7 minutes... Now that I've seen this, I can make that and other processes even better!

@@Adam_K_W FANTASTIC!!!! It’s always great to hear a PQ success story.

I'm not a fan either. Thanks for watching.

You're very welcome. 👍👍

I, like you, retain the [Name] column from the meta data. This was to demonstrate how to achieve the goal if you didn't take that approach. I used to rely very heavily on VBA for me cleanup and automation solutions. Now it only gets used for situations where Power Query can't do the job. MY need for VBA has declined about 90% since Power Query came to being. Thanks for watching.

Thank you so much for saying nice things. Thanks for watching!!!

So glad you're enjoying them. I know it's not a format for everyone. Thanks for watching.

So glad to hear it helps. Thank you so much for watching and commenting. It really helps.

Thanks for taking the time to watch. 👍🏼

@@bcti-bcti no problem... I enjoyed it🤗

Yeah, but's a "good" kind of work. Very satisfying. Thanks for watching.

Great idea! As this is not a 100% perfect solution, there are always going to be scenarios that require creativity. Thanks for watching!!!

I have the exact same problem and your solution works for me. I can't say how much I want to thank you, saving me so much time and nerves! 😄🤗

@@ladytigereye6145 🤗

@@ladytigereye6145 so glad it helped. Thanks for taking the time to watch.

@@bcti-bcti The crazy thing about those "helper queries" is: If you use them, it prevents you from building a Power BI template app. Power BI template apps don't allow parameters of the type "any" or "binary". So without eliminating those helper queries from the data model one is not able to use an app to publish Power BI reports built from that whole data model. That's a very annoying relation I had to uncover. 😅 Thanks for helping me solve this issue. My app is running now. 😁

That is exceedingly nice of you to say. Thanks for taking the time to watch.

Glad we could be of service. Thanks for your viewership!

Thank you so much. We appreciate your viewership.

Thank you so much for taking the time to watch and comment.

So what's the NEXT project I can assist with? 🤣 Thank you for taking the time to watch.

Much appreciated!

I haven't run any scientific studies, but I can't help but think that things would run a bit quicker without the extra overhead. I'm sure someone could demonstrate a scenario where having the helper queries improves performance, so I guess it's just a matter of what you're willing to tolerate. Thanks for watching!

Thanks. I couldn't agree more.

You are very much welcome. Thanks for watching.

I like to think I know more than most about PQ, but I still have a LOT to learn. It’s always a great feeling to learn something new. Thanks for watching.

I can whole heartedly second this comment. I have reasonably good PQE skills but you sir are next level!!!

Thank you so much!!! We appreciate you taking the time to watch and comment.

@@bcti-bcti you ought to have an idea how much I and other users here appreciate the time you took to create content that is not only fun to watch - and learn from - but adds so much value to our lives.

@@AJ-fd3yj thank you for the encouraging words. It makes me want to try harder with each video. Thank you.

Thank you for the nice encouragement. We hope it thrives, too. If you want to post here, or email me your full query (copied from the Advanced Editor), I can take a look and see if there's anything that may come to mind when it comes to optimizing the performance. training@bcti.com

Very true. Thanks for the input. And thanks for watching.

This isn't part of an online course, it's just me making videos that I find interesting. The company I work for does perform training for other companies, but they're typically local (i.e., within 150km), but we do have some students that take the class over the Internet. The class(s) are live and not recorded. It's the same experience for students in the classroom versus remote. Time zones are also an issue with live, distance-learning sessions. We're flattered that you would wish to take a full course. Where are you located?

I am located out side the U.S... Which of the trainings is related to Power query?

Thank you for saying so. And thanks for watching.

Thanks for the compliment. We appreciate you watching.

You're welcome. Thanks for watching.

Yep. Sure is. Thanks for watching.

Without seeing your data, I would think that you would just have to rename the date column to something more appropriate. Are you losing the entire record when you promote the header row, or just a single heading for a single column?

Can you provide a sample of M-code to demonstrate your combined strategy? Thanks.

8:55 I'm on my phone so just typing out this line as it is what I'm referencing. I'll type out the full M when I get to my computer later today. Please forgive any typos. I'm currently on a crowded L heading into downtown Chicago. I think you will be able to understand from this. =Table.ExpandTableColumn{#"Removed Columns", "CSV_Data", ("Column1", "Column2", "Column3", "Column4"), ("TranDate", "Account", "Dept", "Sum of Amount")

I thought you were alluding to that, but I wasn't sure you were doing something else. Yes, that would be a great step-saver. I should have done that in the video. Thanks for the input, and thanks for watching. @@michaelt312

@@bcti-bcti , your channel is awesome. I tripped on this process when I was trying to clean some code. It is now my go-to in every build.

Thanks. I haven’t done any large scale performance tests, so I’m uncertain as to the benefit/detriment of either approach. Thanks for watching.

If you look at 12:08 in the video, I show how to use the strategy on Excel files using the Excel.Workbook() function. Thanks for watching.

@@reggydavis You could, but it’s my experience that many users don’t like all the additional overhead and would rather take matters into their own hands. Thanks for watching.

You are most welcome. Thanks for watching!!!

replacing .Files with .Content works for Sharepoint folder as well, with small condition: you'd better be site admin.

Yes, exactly. I would just start at the sub-folder level, but this gives the operator the ability to "catch" more files that may be spread across multiple folders. It also allows the video to demonstrate "path filtering", something some users may have never thought to do. Thanks for watching.

Once you have the folder data reduced to just a [Content] column, you could create a custom column using the following formula: =Xml.Tables([Content])

Glad it helps. Thanks for watching.

You could keep the [Name] and [Content] columns, that way you could use the [Name] as a transaction-level "stamp" so you always know where each transaction came from. See the below M code for an example (this is done with the CSV files, but can easily be done the same way with any files.) ============================== let // Connect to the parent folder holding all files and subfolders Source = Folder.Files("C:\Camtasia Projects\Power Query - Avoid Scary Queries"), // Standardize the casing of the file extensions (using UPPERCASE) Uppercase_File_Extensions = Table.TransformColumns(Source,{{"Extension", Text.Upper, type text}}), // Filter to keep only files in the "CSV Files" folder and subfolders Filter_By_Path = Table.SelectRows(Uppercase_File_Extensions, each Text.Contains([Folder Path], "\CSV Files\")), // Filter to keep only CSV files Filter_By_CSV_Files = Table.SelectRows(Filter_By_Path, each Text.Contains([Extension], ".CSV")), // Remove all columns of folder metadata EXCEPT the [Name] and [Content] columns Keep_Only_Content = Table.SelectColumns(Filter_By_CSV_Files,{"Name", "Content"}), // Standardize the casing of the Filenames (UPPERCASE) #"Uppercased Text" = Table.TransformColumns(Keep_Only_Content,{{"Name", Text.Upper, type text}}), // Extract the tables from the Binary files Extract_Tables = Table.AddColumn(#"Uppercased Text", "CSV_Data", each Csv.Document([Content])), // Delete the [Content] column (column of Binaries) Delete_Binaries = Table.RemoveColumns(Extract_Tables,{"Content"}), // Expand the table columns of data from the [CSV Data] column Expand_Tables = Table.ExpandTableColumn(Delete_Binaries, "CSV_Data", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}), // Promote the first row of data to a Header Row position Promoted_Headers = Table.PromoteHeaders(Expand_Tables, [PromoteAllScalars=true]), // Set the Data Types for the columns Set_Data_Types = Table.TransformColumnTypes(Promoted_Headers,{{"TranDate", type date}, {"Account", Int64.Type}, {"Dept", Int64.Type}, {"Sum of Amount", Currency.Type}}), // Remove any redundant headers and total rows (rows with text are rendered as errors by the previous step) Remove_Error_Rows = Table.RemoveRowsWithErrors(Set_Data_Types, {"TranDate", "Account", "Dept", "Sum of Amount"}) in Remove_Error_Rows

All steps should be the same for .TXT files as they use the same connector for decoding. The only issue may be when you are using a TAB as the file's delimiter instead of a COMMA. If this is the case, you will have to modify the Csv.Document function as follows: Csv.Document([Content], [Delimiter=" "]) (There is a TAB character between the double-quotes.) NOTE: The "Custom Column" dialog box will not allow you to type a TAB character in the formula; it interprets TAB as an instruction to move to the next field in the dialog box. For this reason, you may have to type the above command in something like Notepad, then copy/paste it into the "Custom Column" dialog box. Hope this helps. Thanks for watching.

I can't be entirely certain as to your question. Can you rephrase it with a procedural example? Thanks for watching!

You could do something like the following: let Source = Excel.Workbook(File.Contents("C:\Excel Data.xlsx"), null, true), // Filter the Excel file's metadata by the name of the desired table (ex: "MyData"). #"Filtered Rows" = Table.SelectRows(Source, each ([Item] = "MyData")), // Extract the data from the "MyData" table MyData_Table = #"Filtered Rows"{[Item="MyData",Kind="Table"]}[Data] in MyData_Table

@@bcti-bcti thank you i will try it... i was actually thinking about something like that, i didnt have the time yet to try iy! thank u!!!

Thanks. We appreciate you taking the time to watch.

Thank you for saying so. We appreciate you taking the time to watch.

Then a different strategy will need to be employed. Every scenario is different and requires its own special adjustments. Thanks for watching.

You are quite welcome!

I'm tied up for a couple days, but let me look into this and see what I can figure out.

@@bcti-bcti Thanks a million. Would help a lot. Currently I import 15 files and it creates a lot of Helper Queries.

@@ExcelWithChris I can assist with this, but it would be easier if I were to call you. We could share screens and I could walk you thru the process. That way, if there's a glitch, I can see what is happening and we can figure it out. You can email me at "training@bcti.com". cheers.

​@@ExcelWithChrisdo you mean 15 files of different structure each?

Thanks for watching!!!

I hate to sound dense, but I'm unclear as to your question. Can you rephrase it, or provide an example? Thanks.

@@bcti-bcti my bad sorry, check in 13:02 you extract the content tables in new column. In my case when I’m trying to extract the content I got table but it’s not belong to my data sheet, that table with 2 columns the first one referred "name" and sometimes "record" and second column is "data" in first row is the table that related to my sheet. How can I make expression to get the table that inside content. In my case usually comes second column inside the content and first row ? I hope that’s clear if not i can share screenshot to ur email.

@@Hemo2YoYo yes, send a screenshot to training@bcti.com and I’ll give a look. Thanks.

@@bcti-bcti i shared the video to your email.

Yes; the tables within each Excel file contains a table that was named according to the state the date is related.

If you look at 12:08 in the video, I show how to use the strategy on Excel files using the Excel.Workbook() function. Thanks for watching.

@@bcti-bcti thanks so much

Thanks for taking the time to watch.

Thanks for watching.

Can you provide additional information regarding your question? I am uncertain as to what you are asking. Thank you.

at 5:52 you'll select Content column first, just as on video, then you hold Ctrl key and click on Name column to add it to selection. then you follow the video and add "Remove other columns" step

Thank you!!! That means a LOT to me.

I am unable to replicate your issue with the example provided. Can you provide additional information? A copy of the M code would help.

@@bcti-bcti I'm honestly not sure what to share, the steps are identical to what you've done above, but this also happens when using the Combine button, so it's not related to the method. I don't think I can share actual account codes, but to show their length (which might be the issue?) they are 1234567.1234 (with various numbers) and some have subledgers which show as 1234567.12345.123 (weirdly these show correctly in PQ). The codes with only one . in them seem to be rounded away from their actual values as shown in the original excel files (which appear to be stored as text as well), and become 1234567.1234000002 (five 0s) or 1234567.1233999998 (five 9s). The data first shows after the expanded columns step to show the actual table data so there are no transforms/promoted headers changing anything, the data seems to be being read this way? Really appreciate your help/insight on this, I can't really find anything about it online but it's very frustrating to have your ledger and subledger account codes being changed upon import!

​@@bcti-bcti Seems my reply disappeared, oddly. In terms of the code, it's literally exactly what you're doing here to the letter, so I think the issue lies with the data perhaps. A text field is brought in from excel which contains the ledger codes in the format 1234567.1234 or 1234567.1234.123 (ledger and subledger). Subledgers seem to come in fine and remain correct, however the ledgers with only one . get weirdly rounded to either 1234567.1234000002 (with five 0s) or 1234567.123999998 (five 9s). In the Excel files which are being brought in the ledger just fixed at .1234, there's no numbers after that, no rounding etc. Hope that makes some more sense? In terms of clarity of the situation, the issue makes no sense! Thanks