Ruth - This video solved so man problems I was having bring in some data. Thanks! How about a video getting this data from a folder with multiple workbooks containing multiple sheets.
+John Sullivan Hi John, in this video: m.ua-cam.com/video/PlSKlFGg-JQ/v-deo.html I show how to do that. Check it out (around min 26). I will make a separate tutorial so it is easier to find. Thanks for the feedback! /Ruth
Superb tutorial. Could you please guide us with the similar situation but every sheets may have different number of columns. Then how would you combine the worksheet after finishing the data cleansing. For example Sheet 1 may have 20 columns and Sheet 2 may have 25 columns and Sheet 3 may have 20 columns and so on. Appreciate your guidance in this
Hi Mirza, How to do it would vary depending on how your data looks like. To get specific help, please post in the power bi community. Have a great Friday! /Ruth
Hi Ruth, I get the error message below. Can you help me please? An error occurred in the ‘’ query. Expression.Error: The key didn't match any rows in the table. Details: Key= Item=01/02/2018 Kind=Sheet Table=[Table]
I recorded the query using File 1 and tried to apply this function to File 2. File 1 and File 2 have the exactly same format and I also changed the path in the M code, but the results were all "null." Can you think of any reason for this and how to fix it?
Curbal yes, when I used the original code with the file 1 name and path, it worked, but when I edited the code to file 2 and path of the file 2, the results were all "null". File 1 and 2 have the exact same format only with different data..
+Junsuk Lee Ok, so code works for file 1 but not file 2. It sounds like you are filtering out your data in the steps. Can you try to step-by-step redo the code from file 1 to file 2? That way you will see where your data "goes missing" in file 2. Sometimes a blank or a mistake in the data can wipe your dataset if you are filtering things out. /Ruth
Hi Ruth, Changes in my source files are not making it over to the tables created with the custom import function. Have I done something wrong or is this a limitation of invoking a custom function in a query?
The solution, although I don't understand why it worked, was to create a new custom function using the same steps. Copying the m-code from the advance editor didn't work. I suspect the function caches the data somehow?
+Emin Uzun Yes, I took it out from the beginners video as I think it is a great trick that can be missed by those that dontt want to watch a long video ;) /Ruth
A nice tutorial. Actual function creation starts at 20:00.
the real hero 😁
you just saved me hours of extra work! Thanks!
🎉🎉🎉🎉
/Ruth
I am new to power query and Powerpivot hope to use BI soon , love your DAX Fridays you are a star
+Mark Dawson Hi Mark! Thanks 😊
/Ruth
Ruth - This video solved so man problems I was having bring in some data. Thanks! How about a video getting this data from a folder with multiple workbooks containing multiple sheets.
+John Sullivan Hi John, in this video:
m.ua-cam.com/video/PlSKlFGg-JQ/v-deo.html
I show how to do that. Check it out (around min 26). I will make a separate tutorial so it is easier to find.
Thanks for the feedback!
/Ruth
I had watched that video but I forgot it included those steps. Perfect! Keep up the good work!
+John Sullivan I will do a separate video when I am back from my vacation!
/Ruth
Thanks! Hi Alfredo!
Hello Hope you are doing well, this video it solved my purpose. I really thank you for posting this one.
Yey!!!! ...and thanks for the feedback :)
/Ruth
Awesome tutorial Ruth , than you very much
+Heru Monas You welcome Heru!
/Ruth
Very nice cool explanation. Thanks a lot
Lovely to hear
Very Well Done!
A very and complex work, simplyfied!
Thanks a lot!
gab
You welcome Gab!
/Ruth
@@CurbalEN / Ruth, I 'm seeing that you answer each one of comment, that's great Too!
@@gxsoft UA-cam does not show me all the comments, but the ones I get, I answer! My favorite part of doing UA-cam :)
/Ruth
Thank you for this.
Thanks for the feedback Jennifer :)
/Ruth
Superb tutorial. Could you please guide us with the similar situation but every sheets may have different number of columns. Then how would you combine the worksheet after finishing the data cleansing. For example Sheet 1 may have 20 columns and Sheet 2 may have 25 columns and Sheet 3 may have 20 columns and so on. Appreciate your guidance in this
Hi Mirza,
How to do it would vary depending on how your data looks like.
To get specific help, please post in the power bi community.
Have a great Friday!
/Ruth
Done posting in Power Bi Community
Perfect! Have a great weekend!
/Ruth
Thank you. that's help me a lot!
Wonderful to hear :)
/Ruth
helped wonders!
Glad to hear :)
/Ruth
Useful video
You are the best!
😊
Hello,
What if I Had similar CSV files instead of one xlsx , how should I do it and will it work ? is it even possible ?
Hi Ruth, I get the error message below. Can you help me please?
An error occurred in the ‘’ query. Expression.Error: The key didn't match any rows in the table.
Details:
Key=
Item=01/02/2018
Kind=Sheet
Table=[Table]
Like this
I recorded the query using File 1 and tried to apply this function to File 2. File 1 and File 2 have the exactly same format and I also changed the path in the M code, but the results were all "null." Can you think of any reason for this and how to fix it?
+Junsuk Lee So, file 1 is working but when you add file 2 it returns nothing?
/Ruth
Curbal yes, when I used the original code with the file 1 name and path, it worked, but when I edited the code to file 2 and path of the file 2, the results were all "null". File 1 and 2 have the exact same format only with different data..
+Junsuk Lee Ok, so code works for file 1 but not file 2. It sounds like you are filtering out your data in the steps.
Can you try to step-by-step redo the code from file 1 to file 2? That way you will see where your data "goes missing" in file 2.
Sometimes a blank or a mistake in the data can wipe your dataset if you are filtering things out.
/Ruth
Curbal Thanks Ruth, I will try the re-do to the file 2 and see the difference in the code.
+Junsuk Lee Hope it worked!
/Ruth
Hi Ruth,
Changes in my source files are not making it over to the tables created with the custom import function. Have I done something wrong or is this a limitation of invoking a custom function in a query?
Something wrong I am afraid...
@@CurbalEN Okay. Not sure what since the initial query worked just fine. I am using Power Query in Excel if that makes a difference?
This is my function:
(Installation)=>
let
Source = Excel.Workbook(File.Contents("***************************************.xlsx"), null, true),
APG_Sheet = Source{[Item=Installation,Kind="Sheet"]}[Data],
#"Removed Top Rows" = Table.Skip(APG_Sheet,3),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Installation", "Site/Subdivision", "House Number", "Unit Number", "Direction (N/S/E/W)", "Street Name", "Street Type (Rd/BLVD/ST/Lane)", "City", "County", "State", "ZipCode", "Year Built", "Transfer Date", "Official Historic Home (Y/N)", "Major Reno", "Medium Reno", "Minor Reno"})
in
#"Removed Other Columns"
The solution, although I don't understand why it worked, was to create a new custom function using the same steps. Copying the m-code from the advance editor didn't work. I suspect the function caches the data somehow?
Did you write a function or :)) I will watch it when I get home but I am curious :))
+Emin Uzun Hi Emin, yes I did ;)
/Ruth
You have recorded it before a month :))
+Emin Uzun Yes, I took it out from the beginners video as I think it is a great trick that can be missed by those that dontt want to watch a long video ;)
/Ruth
Curbal I saw that video, and said, nope this one is not for me :))
+Emin Uzun I understand ;)
/Ruth
Challenging .... i will try it on our own workbooks
+Renier van Gelooven Really useful trick, hope it works for you too!
/Ruth
I don't understand why the variable "city" was used when applying the modified code? Does City = Sheet Name? HALPPP!
Yes :)
Can you explain the city variable? It’s not equal to the actual sheet name right? Is just the dummy name?
sir voice is too low.
Your voice is too low
Sometimes technical glitches happen :(