MSPTDA 36: How To Build Sample File Parameter For A Custom Function to Transform 12 Cross Tab Tables

[@Xcwizard]

I wish you all the best this holiday season and throughout the year, Merry Christmas! Mike, another great technic, again and again, thank a lot. I have a bit easier way to create a Parameter. 1. A01 Create Binary Query from =File.Contents(Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1]) 2. B02 Create New Parameter Type: Binary Default & Current Value A01 3. Advanced Editor to edit B02 Change from Type="Binary" to Type="Any" 4. Add step to A01 = Excel.Workbook(Source)[Data]{0} 5. (06:45) Create Query with Function M Code Based on Parameter. Lots of Transformation Steps.

[@excelisfun]

bo rydobon, Reading what you wrote in your comment, I am not following what the steps are or how it is easier. In general, I am not that smart, so it is probably just me not understanding you.

[@excelisfun]

bo rydobon, I tried to follow your steps, but had trouble with Step 2 because the defaults would not work with selecting Binary. I changed it to suggested value ant value and got the dialog box to accept that step. Steps 3 and 4 worked, but in step 5 it would not let me use parameter to create a function, either by right-clicking and Create Function or my a new query. I am not sure what I am missing.

[@Xcwizard]

@@excelisfun My bad, I'm not a good explainer. You are very smart and the best teacher. All my Excel knowledge comes from you. I make Video on this but I don't know if you can see the link kzbin.info/www/bejne/jIeXmWmda9GDjtk

[@excelisfun]

@@Xcwizard Thank you very much for the video! I understand what you mean now. I subscribed to your channel also : ) : ) By The Way, I am not very smart, but that is what makes me work so hard to try and tell helpful stories about complicated topics. If I did not invent the stories, I would never understand myself : ) In what way do you think the steps in the video that you posted are easier? When i watch and try it, it just seems like the steps are in a different order. However, the coolest thing that I learned from your video is how to get the Parameter dialog box to accept a binary value. For some reason, i could not get that to work before i watched your video. Thank you very much : ) : ) : ) : )

[@excelisfun]

@@Xcwizard I understand now how it is easier: much easier to create the binary parameter and get the query to register in the Parameter dialog box. Thank you very much for that : )

[@santiagovillamoreno6821]

Thanks a lot Mike!! I've been studying Power Query for a while and this is the first time I see something like this. Amazing trick! Exactly what I needed

[@dmd2656]

This is singularly the most useful Power Query video I've seen in 2022. Thank you!

[@excelisfun]

You are welcome!

[@excelisfun]

Topics: 1. (00:01) Introduction 2. (01:38) Get Sheets into Power Query. 3. (04:20) Create Sample Sheet 4. (05:00) Create Parameter 5. (06:45) Create Query with Function M Code Based on Parameter. Lots of Transformation Steps. 6. (10:06) Create Custom Function 7. (10:37) Use Custom Function and Finish Final Query. 8. (11:35) Create Report 9. (11:52) Edit Custom Function 10. (12:50) Summary

[@waverider80]

Love your videos. This is yet another masterpiece showing us creative ways to solve problems. Your tip about going into the options to change the settings so it doesn't automatically change types is a big win for me! You are helping me out, one query at a time. Definitely a devoted follower of your videos!

[@excelisfun]

I am glad that help you "one query at a time"!!!! Since Power Query is the greatest invention since the PivotTable, we are definitely having fun, now, Justin!!!

[@excelisfun]

Thanks for your support, Justin, with your comments, thumbs ups and Sub : )

[@zahoorsarbandi2982]

Truly speaking, you are the BEST. There is not a single person in the world who is such an expert and giving free education!!! A big Salute to your efforts and spreading of free education... May Allah bless you with His kindness and give you more strength.

[@excelisfun]

Thank you for your kind words. I am happy to provide free education for the world, Zahoor : )

[@shubhampawar8506]

I've completed your basic excel, advance excel, E- DAB and MSPTDA . In this era no one is going to teach you such depth knowledge in free of cost . You are most generous and kind person I've ever seen . Thanks a lot for everything . I am going to go through MSPTDA again for revision and For more understanding . Godess of knowledge "MAA Saraswati " bless you with knowledge and everything which u want. Please provide me guidance if any in the field of data analysis. Love from India❤️❤️❤️❤️❤️

[@excelisfun]

This is why I like to post for free: because there are some out there who are not afraid to work hard, study and become a master!!! I am happy to help. It has been my goal for 13 years here at KZbin to provide free education for the world. I am not sure I have any more guidance for you.

[@shubhampawar8506]

@@excelisfun Thanks a lot 😊..I am going to go through each and every videoes of yours to strengthen my excel.❤️

[@davidpellatt3501]

I’m going to have to view this again a couple of times because there’s some amazing techniques that I’ve just seen. Hope you and your family have a wonderful Xmas and thank you for all the brilliance you’ve shared with us this Year

[@excelisfun]

Yes, this is a good one for those of us that have to deal with a lot of bad and messy data in Excel. Power Query really does make our lives so much easier : ) You are welcome for the brilliance and happy Xmas to you and your family too : ) : )

[@mohamedchakroun4973]

Automation leads to avoid manuallay mistakes :-) Thanks for all Mike you are the best :-)

[@excelisfun]

So true, so true : ) : ) You are welcome for all, Mohamed : ) : )

[@MalinaC]

Thanks for sharing. The number of techniques I want to learn still grows :). Thanks a lot, Mike!

[@excelisfun]

You are welcome, Malina : ) And, yes, there are so many wonderful new things for us to learn in Excel : ) : ) : ) : )

[@katerina6495]

Thank you for another amazing video Mike, Have a great day Mike 🤗

[@excelisfun]

You are welcome, Katerina!!!!! Everyday is a good day when I can post a video and then get a great day from you ; )

[@katerina6495]

It is a pleasure Mike, It is a great day when I see your super fun videos, they are making life so easier

[@excelisfun]

@@katerina6495 : )

[@shubhampawar8506]

You are unmatchable and genuious teacher ❤️❤️💯💯😊👌

[@mohsinhassan88]

This is an excellent. I have been googling for days to findout how to this. The most difficult bit is when you dont know what you should be goolging.

[@excelisfun]

Glad it helps!

[@chrism9037]

Very impressive Mike!

[@excelisfun]

Thanks, Chris : ) : ) : )

[@SyedMuzammilMahasanShahi]

This is EXCELlent. Thanks Mike for this amazing video.

[@excelisfun]

You are welcome for the fun, Syed : ) : ) : ) : )

[@enriquedominguez9709]

Kudos for you and for Bill, Mike. Simply amazing.

[@excelisfun]

You are welcome, enrique!!!

[@zhouyinwei2007]

Just suffered days before when trying to edit a customized function by adding (input table)=> code, when an error happens, I can only try to edit it in Advanced Editor which sometimes difficult for a beginner , and now the solution comes, I love the whole course, thanks Mike again ~~

[@excelisfun]

Yes, the whole course does offer a lot. Glad you love it, zhou!!!

[@wayneedmondson1065]

Great one Mike! Thanks for the M code wizardry with parameters and custom functions. Thumbs up!!

[@jameszhou162]

The only way for me to really master the technique you taught is teaching to others. Merry Christmas Master Mike!

[@excelisfun]

Yes!!!! You go and teach others, then they benefit and you do too : ) Go Team!!!!!!

[@roywilson9580]

Thanks Mike, another great video full of useful tips and tricks. This is going to be so useful to those of us that have ever had to deal with badly constructed datasets passed to us by clueless management teams! Keep up the good work, your videos are very appreciated! Hope you have a great Christmas and look forward to more great videos in the new year.

[@excelisfun]

Yes, the problem comes from Excel Education. Most College and University classes do not strongly emphasize Data Analysis and Proper Data Set Concepts and so we get managers and workers who keep data in all sorts of ways. The Cross Tabulated Layout is very common. Too bad. It just takes a few simple lessons during the first week of a class, then repeat throughout the quarter, and BAM, no more storing data in Cross Tabs or other non-Proper Data Sets... Merry Christmas to you, Roy! I can't wait for a new year of Fun Excel Videos : )

[@roywilson9580]

@@excelisfun so true, data structuring should be a major part of any college course. Even worse than cross-tab format is the guys that insist on leaving blank rows and columns and then merge and centre headings to make the data "look nice", that is my pet hate.

[@excelisfun]

@@roywilson9580 O, ya, the dreaded merge and center and blank row and blank column: they think they are helping... but big : (

[@roywilson9580]

@@excelisfun With that kind of help, who needs sabotage? lol

[@TheMoh148]

Hi, To pull the query directly, Change the Type from Any to *Binary*. You are the best. Many thanks, Mohammed from Algeria

[@DIGITAL_COOKING]

I start to think that power query is the most important tool especially for the accountants, Excellent video mike, I love MSPTDA 👍🙂

[@excelisfun]

Yes, me too. Power Query is clearly the best tool invented since the PivotTable!!!

[@shipingli7590]

Thank you, Mike. And Merry Christmas.

[@excelisfun]

You are welcome, Shiping Li! Merry Christmas to you too : )

[@Softwaretrain]

Woooow, I was surprised with the way that you made this parameter😍😍😍. Last night I tried a lot to set this parameter but couldn't find a way. Edit Parameter was something that I never see in other sources. Clever way and hope Bill Szysz have UI solution as always to complete this great tutorial. As always I learned something new in your video and enjoyed it. This time you are poet of power query. Thanks my lovely teacher.

[@excelisfun]

What do you mean "Edit Parameter"? Do you mean that I went to Advanced Editor and edited the code? I hope Bill Szysz has a smoother way to do this. I have been trying to use parameters for a few years, and it just never seems easy... : ( But, as long as we can create the parameter, typing it out or not, it is usually better than the way we did it in EMT 1621 : )

[@brianxyz]

@@excelisfun Mike, when you were editing the parameter (starting around the 6:09 mark) you added BinaryIdentifier as an element to the record. Where did you find a list of the elements that you could add and does it matter where you add them in the record? You seemed to pick a specific spot but didn't indicate why.

[@excelisfun]

@@brianxyz I could not find a listing of the elements for this online. I searched Microsoft, but did nit find it. The way i discovered it, was when I did an automatic Text File From Folder clicking of the "double Arrow" to expand the Text File, and then I went and looked at the code for that, and copied it, and played around with it. You are right, I did place it in a specific place, but it was just based on what I saw in the automatic From Folder code. The problem with Microsoft, is that they make such great tools and features for us, but then I do not know where they keep the "How To Section" for how to use the tools and features. If you think about this history of Array Formulas, the programmers of the spreadsheet, actually programmed the spreadsheet to do all these amazing things, but then they did not post anywhere, about what was available or how to use it. We all just had to try stuff and accidental find stuff... It seems the same today with Power Query and M Code. But I might be wrong, but I just do not know where to look or read to find out the definitive list of how all this works. So then it is just all of us bloggers, who try stuff and post and get some things right and some things wrong.

[@Softwaretrain]

@@excelisfun Yes I mean edit code of parameter. I like it. Clever guy with clever solution. Thanks again.

[@ndjanardhan]

Great,Helpful & Time Saving Video, Thanks a lots Sir for the saving time of us. God Bless You. Thank You Very Much. 😀😀😀❤❤.

[@excelisfun]

You are welcome very much, Janardhan!!!

[@aminshahin7919]

Thanks a lot for the video and wish you and your family a merry Christmas and happy holidays👍👍

[@excelisfun]

Thanks, Amin! Happy Holidays to you and your family : )

[@DeenQuery]

excellent demonstration of how useful and powerful excel is...

[@excelisfun]

Glad you like it, Nima : ) : )

[@davebowman5392]

Great video Mike, thanks

[@excelisfun]

You are welcome, Dave Bowman! Do you ever have a need for this sort of thing?

[@ajayganeshie1857]

This is great work man! Thnx tot the lesson.

[@excelisfun]

You are welcomex tot the lesson, Ajay : ) : )

[@EricHartwigExcelConsulting]

Very awesome video! I have already shared it with two of my friends that deal with messy data on a regular basis. I hope you and everyone else has a great New Year's!

[@excelisfun]

Thanks for the share, Eric!!!!! Happy New Year to you : )

[@meongcaem5802]

Amazing video... thank you so much for making this video... 🙏🏻

[@excelisfun]

You are welcome so much for the video, Gue Caem!!! Thanks for your support with your comments, thumbs ups and Sub : )

[@johnborg6005]

Thanks mike. Alot to absorb in one view :) :)

[@excelisfun]

Yes, it is. But good stuff : ) Thanks, John!

[@wmfexcel]

Mike, this is awesome!! After a few attempts, I made it! Btw, here's my learning: Three simple steps 1) Create Sample File 2) Create Parameter 3) Build Function I will keep in mind these three steps. For step number 2, when I named it with 02SampleFile, I got error when editing in Advanced Editor. Apparently the Parameter Name cannot start with a number. For step number 3, instead of making a new query and type the query name in formula bar, we can simply right-click the parameter query and reference it Also I learned that by converting the column into List, and then use Table.Combine, all the data types defined in the function query propagate to the result. What a cool trick! :) This is the best gift on the Boxing Day!

[@excelisfun]

Yes, the naming thing... that is why I named it A01, B02 and so on : ) Like the right click that you mentioned: Right-click then use Parameter : ) The "converting the column into List, and then use Table.Combine" is a trick I learned from Bill Szysz to avoid the hard coding of column names into formula when you use the expand button. Happy Boxing Day to you, M F Wong - I am so happy that you like the gifts in this video.

[@arnaudseguin6404]

Thank you Mike for another great video

[@excelisfun]

You are welcome, arnaud!!! Thanks for the support on each video with those comments and thumbs ups, and of course your Sub : )

[@barttitulaerexcelbart9400]

Wow, all those steps, amazing. Difficult to remember them all, but when I need them I will definitively look again in this MSPTDA... Mike a question: how do YOU acquire all this information how to do this? Just try something would cost a lot of time...

[@excelisfun]

Because there is no really authoritative source or book or web site for Power Query, the way I learn is you the "sledge hammer" method. I "just try things" as you say, sometimes I create the same query many times, and each time I try to think of it a different way and try it a different way. I ask Bill Szysz to help me often, because he is the smartest Power Query person that I know. I read Microsoft Power Query Help. I sometimes will post to Power Query forums, but the Microsoft one is often so bad; but the mrexcel.com can be okay. I listen to the viewers of my excelisfun videos who comment because they very often have great tips about how to use Power Query and M Code efficiently, and more... However, Bart, since this is the new frontier of data analysis with Microsoft tools, it really is just hit and miss until we all, as a Team, can find the efficient and fun methods to perform out Power Query tasks : )

[@excelisfun]

Another thing too, for this video I tried to search online and find out information about the arguments in the parameter record, but I could not find anything. I tried to be diligent in my search words, typing things like "power query parameter record arguments", but I could not find an article or Microsoft Help site to tell me what the arguments were. The only way I created this code (where I inserted the second argument in the record): A01SampleSheet meta [IsParameterQuery=true, BinaryIdentifier="A01SampleSheet", Type="Any", IsParameterQueryRequired=true], was by creating a From Folder query, where the folder has text files, and then using the expand button, I had the User Interface create the code and the parameter. Then I looked at it and saw that there was the second argument: BinaryIdentifier="A01SampleSheet". Just as when we learned about Array Formulas, many years ago, now with Power Query, we just hunt and peck and infer the logic of how things work...

[@JoshuaDHarvey]

Brilliant Mike, we love your pioneering efforts and willingness to share with those who stand behind your bravery/courage to find answers and cheer you on so we can learn from you!!!

[@spilledgraphics]

MIND--- BLOW----ING !!!!!!!! Amazing Mike,!! amazing !!!

[@excelisfun]

Glad you are blown, Teammate!!!

[@darrengodkin]

Excellent video again Mike!

[@excelisfun]

Glad it is EXCELlent, darren : )

[@nboisen]

Brilliant!

[@excelisfun]

Glad it is brilliant for you, nboisen!!!

[@simfinso858]

Awesome video

[@excelisfun]

Glad you like it, SIMFINSO!!!!

[@Softwaretrain]

I am the first comment, I was eagerly waiting for this tutorial and I am sure it will really informative for me before see it. Thanks and I will put another comment after watch it.

[@excelisfun]

Thank you for your amazing support, Software Train : ) : )

[@Computergaga]

Awesome!!

[@excelisfun]

Thanks, Computergaga!!!! BTW, I never mentioned this before: I LOVE your username: Computergaga : ) : ) : )

[@Computergaga]

@@excelisfun Thank you 😀

[@PatOrchard]

Nice crisp video. At 9:10 you unselect null. That will cause problems when the data changes. You need to use number filter not equal to null.

[@excelisfun]

Thank you for that good tip : ) What is the problem that filtering out null will cause? What about number filter not equal to null solves the issue?

[@PatOrchard]

When you unselect null, what is left is a selection of specific values. That is what will be selected in the future. Any new values will not be selected. If you instead filter values not equal to zero (or null) you'll get all the values.

[@excelisfun]

@@PatOrchard I thought that when we unchecked null it added a not equal to null logical test!?!?! Like: = Table.SelectRows(PreviousStep, each ([Column] null))

[@PatOrchard]

@@excelisfun Ha. I just checked and you are correct. If one is using an older version of Power BI, I'd suggest being careful, but the current version understands what you mean when you deselect a value. With the current version, you can do it either way.

[@hosseinhosseinpoor9561]

خیلی عالی

[@damildev]

Thanks for the opportunity, I have followed the series to the end but as for the episode 36 I didn't get the final answer, it kept on invoking on just one table. Please help me.

[@JoshuaDHarvey]

Mike since publishing this have you found or learned more about how this code is executed. I’m trying to visualize how the parameter is used when you execute the function across each table and I can’t seem to wrap my head around that. Thanks in advanced for any additional insight you can offer and thanks for an awesome video!!

[@JoshuaDHarvey]

Does the parameter and sample file not actually matter at the end, they are just used to build the function so there is no reference back to that parameter once your invoke the function across each table in the Data column?

[@DavidGzirishvili]

Brilliant! One question: why append the tables? Cannot we expand the "Data" column? Is there any advantage in appending? PS. The reason I ask is that I needed to "carry forward" worksheet names, i.e. values of columns "Name" or "Items"... and it was easier to get a column worksheet names in the final table using "Table.Expand"....

[@zaafirc369]

Great video. I have a question regarding combining data in power query. Is it possible to append a new query(new table) to an old one(large master table) without loading the large master table again? I have to add a new file each month to a dashboard. Is it possible to combine this new file to the other historical files without reloading the historical data again on the dashboard?

[@ExactProBi]

Thanks, Mike as always, but when we use to combine data option while importing data from a workbook, Power Query does this automatically I suppose, but creating manually like you have done give us more control!

[@excelisfun]

That is not correct. It can not automatically unwind our cross tabulated tables. But the automatic feature can work in some situations when the objects are tables. It is really only reliable with Text files. Microsoft makes the automatic feature available with Excel Workbooks, but is can not take into consideration that many variables that are present with an Excel Workbook and so may not give desired results. The power and non-automatic nature of Custom Functions is what we need to do when we have this sort of non-Proper Data Sets : )

[@ExactProBi]

@@excelisfun yes agree, I thought I was talking about bringing data from CSV files or excel workbooks as an individual object, not when all data resides inside a single workbook inform of sheet object! you are correct! thanks Mike

[@dirkstaszak4838]

Hi Mike, super tutorial. I used it today and followed everything almost perfect. I have the sheets in a separate workbook. No problem until the step InvokeCustomFunction. I have 3 columns plus the result from the function generated. If I use the Table.Combine step. I do loose the other information, that I need. How can I put this step into the line above in order to keep the information. Any suggestion would be very much appreciated. Addition: If I use the described way all data types are in the way required. If I just expand the table I loose them. This I would like to avoid.

[@davidpellatt3501]

Hi Mike, I'd like to understand more how about how to use a variable to reference a table in an M Code formula. I know that you've shown how in a number of Videos but I just can't find one to view again, could you tell me what would be a good one to watch. Incidentally is there a general way to search for a topic that would retrieve a list videos where that topic is included. Many thanks as always. David

[@excelisfun]

The home page of all KZbin channels usually have an introductory video and search option and playlists. In this case you are probably looking for the MSPTDA playlist, which are listed on my home page. the home page is: kzbin.info On this home page you will find the two data analysis playlists, which are the ones that you want. Send me a message at any time if you can't find something.

[@excelisfun]

Here is the specific MSPTDA #9 video that might be the M Code video you are looking for: kzbin.info/www/bejne/hISUcqmci551m6s

[@madhavmehendale7324]

Hi Mike - your channel is absolutely terrific. I keep coming back regularly for more. I have a question - I used the above technique and I hit a roadblock. I have multiple Excel files in a folder. Each file has 2 sheets "Region1" and "Region2". Both sheets have different data, need different transformations and ultimately need to be combined into 2 separate power pivot tables - for Region1 and Region2. So I got a listing of the files from the folder- extracted the excel objects and from this base query, I duplicated 2 queries (each query listed out sheet names that equalled Region1 and Region2 respectively). The idea was to make 2 custom functions (for each region) and invoke the relevant function in the query for each region (fxReg1 for Region1 listing and fxReg2 for Region2 listing). It went fine for Region1 - I could invoke the function and combine the resultant data and load it to the data model. When I got to region2, I couldnt get past making the parameter as the previously made parameter for Region1 kept asking for a default value in the Manage Parameters form - whats more the default value was greyed out. Does this technique only work when there is one file parameter? How can this be extended and modified if you need to add parameter 2. Thank you for your time and attention to my question

[@avit1860]

hii i need to specify a braking point say in cell A1 i will put 8 , and then how can i use it in the m code with custom column? say =if [columnname] > (the value that in cell A1)

[@PetricaR]

Dear Mike, How could I get the data from this link, starting with a specific date and finishing with another one, a time range actually. For example, start_date = 01/Jan/2019 and end_date = 20/Jan/2019 automatically in excel? It seems that the site is showing data using post method. ("www.opcom.ro/rapoarte/pccb_le/rez_PCCB_LE.php?lang=en").

[@PEDRUSCOT]

Great!!!

[@excelisfun]

Glad it is great fro you, Pedro !!!

[@baderobaid9497]

Thank you

[@excelisfun]

You are welcome, Bader!!

[@excelpowerbisinhalen-9606]

Hi Mike, Why this is not working with Power BI Power Query. IWhen I promote the headers for dates it's different in each sheet. So I am getting an error saying "An error occurred in the ‘’ query. Expression.Error: The column '30/12/2019' of the table wasn't found. Details: 30/12/2019 Please help

[@mohamedabdulla7927]

Thank you very much , It's wonderful learning video , If I like to start learning Excel from scratch from your chanel , Is there recommended order for playlist to flow to learn from zero to advance level ?

[@excelisfun]

Here is what i have to offer: Excel Basics: kzbin.info/aero/PLrRPvpgDmw0n34OMHeS94epMaX_Y8Tu1k Excel Data Analysis Basics: kzbin.info/aero/PLrRPvpgDmw0lPPRiJO5dCUratRGpGx3aT Advanced Excel: kzbin.info/aero/PLrRPvpgDmw0lcTfXZV1AYEkeslJJcWNKw Advanced Data Analysis: kzbin.info/aero/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1

[@excelisfun]

I hope you can help support the free education with comments and thumbs ups on each video that you learn from : )

[@JonathanExcels]

I very well could be missing something fundamental so my question is not intended to be critical. My thinking would have been to combine all the data and then clean it up once. Is there a speed advantage to cleaning each each piece of data before combining?

[@excelisfun]

I do not know how to do that. How do you combine the cross tab tables without first converting them to a proper data set? In this way, I see no other way to do it, other than cleaning and transforming each table, first, then combine.

[@excelisfun]

If you can see a way, let us know so we can have more cool ways to do this : )

[@JonathanExcels]

@@excelisfun used your method for the first three steps but then used my method for the rest. Let me know what you think. let Source = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content], GetData = Excel.Workbook(File.Contents(Source{0}[Column1]),null,true), #"Only Keep Sheets that start with W" = Table.SelectRows(GetData, each Text.StartsWith([Item], "W")), #"Removed Other Columns" = Table.SelectColumns(#"Only Keep Sheets that start with W",{"Data"}), #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}), #"Get rid of totals" = Table.SelectRows(#"Expanded Data", each not Text.Contains([Column1], "Total")), #"Promoted Headers" = Table.PromoteHeaders(#"Get rid of totals", [PromoteAllScalars=true]), #"Get rid of headers in subsequent sheets" = Table.SelectRows(#"Promoted Headers", each [#"SalesRep/Product"] "SalesRep/Product"), #"Add a delimiter" = Table.ReplaceValue(#"Get rid of headers in subsequent sheets","Product",":Product",Replacer.ReplaceText,{"SalesRep/Product"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Add a delimiter", "SalesRep/Product", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"SalesRep", "Product"}), #"get rid of blanks" = Table.ReplaceValue(#"Split Column by Delimiter","",null,Replacer.ReplaceValue,{"SalesRep"}), #"Filled Down Sales Rep" = Table.FillDown(#"get rid of blanks",{"SalesRep"}), #"Get rid of nulls" = Table.SelectRows(#"Filled Down Sales Rep", each [#"Product"] null), #"Removed Grand total column" = Table.RemoveColumns(#"Get rid of nulls",{"Total Per Product"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Grand total column", {"SalesRep", "Product"}, "Date", "Units"), #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Date", type date}, {"Units", Int64.Type}}) in #"Changed Type"

[@excelisfun]

@@JonathanExcels That is cool, but I do not see the parameter. Am I missing something?

[@excelisfun]

@@JonathanExcels I read you code, but now I will try it in a workbook.

[@sandeepvohra9263]

Hi Sir, Great Video involving lots of skills. But i think in W(9) to W(12) Worksheets, the Grand total (Daily Totals) misses 1 Sales person,during addition and therefore, its not adding up properly in PIvot Table. Kindly look into.

[@alializadeh8195]

Thanx

[@sajadmk6618]

Hello Sir, Can you please help to solve my doubt. My doubt is nothing but regarding to invoice and payment settlement using excel. If we have issued two invoices of amount 20000/- and 30000/- and we are getting a payment of 40000/-. Then the balance amount 10000/- must show as the outstanding in the second invoice row. Payment must be cleared on each invoices and also excel should work out if a payment is getting after the credit period with an interest rate of whatever we want. Actually I've done that but failed in interest calculation. Waiting for your valuable reply

[@excelisfun]

I am not sure. But try this great Excel question site for back and forth dialog to get Excel solutions: mrexcel.com/forum

[@sajadmk6618]

@@excelisfun Very thanks for your reply Sir...Expecting the solution

[@sajadmk6618]

@@excelisfun =MIN(SUM(L:L)-OFFSET($E$1,,,ROW(E2)-ROW($E$1))),D2)... inv no,inv date,credit period,inv amnt,paid amount,due these all are accommodated in COLUMN A,B,C,D,E,and F. K and L column contains payment date and payment amount...settlement works fine...I need an interest calculation sheet...if a payment is getting after the credit period then for date difference of invoice date and payment date interest should be calculated....considering all the possibilities of payment..sometimes it will be partial payment or full payment. Waiting for your reply... As per the direction from you I have posted my query to the website that you prescribed

[@FRANKWHITE1996]

too advanced for me, but still watched

[@excelisfun]

Thanks, FRANKWHITE1996 : ) : ) : ) : )

[@EricHartwigExcelConsulting]

I learned the hard way that the custom function query names cannot start with numbers only letters or the parameter wont work.

[@excelisfun]

Me too : )

[@chelebarry]

What no entendí nada... Did not understand