Hi MIke, thx for ur teaching. Just to avoid confusions for future users of that great video. After uploading "San Jose" File the Sales for SalesRep "MIki" are missing in the Excelfile and Power BI Desktopin the video- Hopefully nothing personal :-). Its not critic - just hint...Maybe also u updated the excercise Files.
Unbelievable, unique, no-comparison on all media channels. This guy not only gives you everything, but the quality is second to none. He enjoys giving to people, to all people. He replies to all, he respects all subscribers. I have had the pleasures, to exchange comment with Mr. Girvin. He enjoys exchanging with us, as much as we enjoy having a reply from him. Great video, as always, Sir. Greetings from Scorpio of Clearwater.
Practice problems are posted at end of Start file. HW problem #6 is a comprehensive practice problem that pulls together all the teachings in the video - not an easy one - great for practice!!
Mike, been following you for YEARS and recommending your channel anytime somebody asks me how to learn Excel. One point where I differ with you: justification of table headers. I justify the column header to match the data beneath the header. So if a column of data is number, then I right justify the header. If the data is text, I left justify the header. It just makes reading easier - especially for wide columns.
I never heard of that before. But it sounds good to me. Especially since my rule for alignment is solely to help track down data type errors. There are actually many exceptions to the rule of numbers right and text left : ) Yours is a good one.
I do know most of this (due in large part to watching your videos over the last 4 years and practicing) but I want to brush up on Power BI; this is my weak link and something I want to get better at in 2023!
@@chrism9037 MECS video #4 and this one have my best Power BI stuff. If you know Power Query and DAX, then Power BI is easy. Most Power BI users in the world who think they know Power BI do not know Power Query, M Code and DAX at all, which means they don't know Power BI. The hard stuff is all behind the scenes. The visualizing and sharing part in the easy part. I think this video shows why all us Excel users must use Power BI - it is just MUCH easier to build visuals and dashboards with Power BI : ) : ) : )
Made it through the first section so one thrd finished!!! Have to warch in three parts. I totally agree about Pie charts. My absolute rule on number of pie pieces is: #of slices
1:21:24 here we need to be very vvery carful. as Mike mentioned many times, PQ is case sensitive, meaning xlsx is not equal to Xlsx or XLSX. Technicaly this means it is a good practice to use one of the transformations: Text.lower, Text.Upper or Capitalize first letter, and only after to filter to designated extension.
Art = 1) few extraneous elements and 2) every time you come back, you can see a new truth. That is true throughout all history. Here is an example: SUM((CriteriaArray=Criteria)*Numbers) : ) : ) : ) : ) : ) : ) : )
As a person in graphic production in some cases centred text and even right align text is better and clearer to read, I think it just depends on what the text is and the relationship to the column to the right
You are right. For the final reports center is often OK. Mostly solutions with formulas should not have centered numbers - to make tracking errors easier.
1:24:15 here I would use different way: I would use Table.Transformcolumns() function to change the binary object to excel object instead of adding additional column. and then I would use a formula to pass Name into each workbook separetly, and finaly i would use Table.combine to combine list of excel tables from Content column. I work with very hard files so the less columns I generate the easier it is to work with the files.
Mike, you inspired me to learn everything about Lamba and Let. Here is Bingo Card generator code that I created. Its probably simple, but I felt accomplished seeing it work! =LET( name, {"B","I","N","G","O"}, bn, 1, in, 16, nn, 31, gn, 46, on, 61, RandGen, LAMBDA(num,TAKE(SORTBY(SEQUENCE(15,,num),RANDARRAY(15)),5)), B, RandGen(bn), I, RandGen(in), N, RandGen(nn), G, RandGen(gn), O, RandGen(on), topN, TAKE(N, 2), midN, "Free", bottomN, TAKE(N, -2), game, HSTACK(B,I,VSTACK(topN,midN,bottomN),G,O), card, VSTACK(name, game), card )
I did something slightly different. In the Title measure, I didn't create measures for Max and Min dates & referenced them from the measure. From a DAX Studio perspective, it is probably less efficient as it means recalculating the max & min everytime.
Yes. Here is a translation list of serial numbers that will be in my upcoming M Code book: Power Query Serial Numbers Power Query Dates Excel Serial Numbers Excel Dates 2958465 12/31/9999 2958465 12/31/9999 45114 7/7/2023 45114 7/7/2023 366 12/31/1900 366 12/31/1900 365 12/30/1900 365 12/30/1900 61 3/1/1900 61 3/1/1900 60 2/29/1900 60 2/28/1900 59 2/28/1900 59 2/27/1900 3 1/3/1900 3 1/2/1900 2 1/2/1900 2 1/1/1900 1 1/1/1900 1 12/31/1899 Text 12/31/1899 0 12/30/1899 Text 12/30/1899 -1 12/29/1899 Text 12/29/1899 -2 12/28/1899 Text 12/28/1899 -102589 2/12/1619 Text 2/12/1619 -255899 5/15/1199 Text 5/15/1199 -644000 10/13/0136 Text 10/13/0136
According to the article (wiki) , Tufte, has also criticised powerpoint junk. It is good to have some confirmation: I have seen so much ppt junk: people showing off with flying words, crazy transitions, bright yellow writing on light blue. I have never used the stuff, and every time I was told by the show-offs that I should really learn to use powerpoint as I do not even know how to put in a flying sentence. Everybody became a show-off so as not to be told that he cannot use powerpoint. (In the Tufte article, the facts on the Columbia are quite shocking.) - sorry for being a bit off topic -
Ooooo Absolutely: I saw Tufte at a seminar about 2010 and he hammered PowerPoint. He is right. But, PowerPoint is the correct tool to use when you want to help your audience fall asleep... lol I used to have to teach PowerPoint, and I spent one day (since there is really nothing to learn) and I used to show them all the things NOT to do...
1:25:51 that is Fun way of doing it. but also bit harder since you are creating multiple steps. If we could get updatetable Sales representative List of names, (import from sharepoint list, or another separete workbook, or sharepoint location (...) then we could simply filter the Name.1 column via list of values, and each time the would get updated we could easly update the file.
@@ExcelInstructor It is the opposite: I am happy when ideas are shared to help the Team! The more eyes on a problem, the bigger the pool of amazing solutions and ideas : )
Great video, as always. A shorter keyboard shortcut for Conditional Formatting Rules Manager is ALT O, D. It's very nice when there are existing rules to modifiy. Otherwise, it's the same number of steps to get to a new rule. I can't remember where I saw this, but I use it a lot and it's one less key. I don't know the other legacy keyboard shorcuts. If anyone knows of a compilation of those old shortcuts it would be interesting to see if there are any common ones that are two letter combos
You might have learned it here at excelisfun because I used Alt, O, D for the first 7-8 years at UA-cam. Then I switched becasue I am crazy with keyboards - I measure each one to see when is easier. I use Alt, H, L, N when I create a new rule because if you use Alt, O, D you have to use this full keyboard to get to New: Alt, O, D, Alt + N. Whereas with Alt, H, L, N - you do not have to stop and add a second Alt to go with N. Alt, H, L, N is more sooth for me because I just tap Alt once, then H, L, N in succession. I use Alt, O, D when i already have Conditional Formatting Rules, and that keyboard gets me to list of rules. Hard core. Sometimes I do not know how I remember them all... There is a compilation: it was in one of Mr Excel's Excel 2007 books: he had a full many-pages-grid of all the old ALTs and all the new ALTs. I can't remember which book, but I do have it on my shelf of 175 Excel books at work...
I just got to work and found the book: Special Edition Using Microsoft Office Excel 2007 by Bill Jelen pages 78 - 85 have a list of all legacy Alt keyboards. This book is the most complete Excel book I have ever seen. It has everything: every function, every menu item, every ribbon item, every keyboard.
@@excelisfun Mike, those are great points. Alt O,D is great for editing existing formats, but I have to concede Alt H, L, ? gets you to the particular option you want. I'm switching to keyboard shortcuts more and more and after the initial adjustment, especially for navigating around the worksheet. There are other combos I use (Alt-H, Alt-A and Alt-M branches) to where I almost forget where the icons are on the ribbon. For example, I hardly ever use CTRL + V anymore, and instead one of the variants of Alt H, V, ? I appreciate the way you continue to integrate keyboard shortcuts into your videos.
1:19:22 That is true that all of them are objects that can be vied in PQ when doing the import. However, when importing to power query in excel, some files have "Magical Object" in it and by magical object i mean a secondary exact same data set, that can be viewed when importing data to power query from excel workbook, however when looking in name manager, file info, and so on this object cannot be found. however this VBA Code: Sub DeleteNamedRanges() Dim MyName As Name For Each MyName In Names ActiveWorkbook.Names(MyName.Name).Delete Next End Sub will remove all objects including that "magical object" this magical object is something generated when exporting datasets from external softwares like SAP ot Tririga or even Trintech. I do not know if n SAP 4 hana this problem persist. it is problem in some SAP s3 versions.
6:48 sorry but I disagree with a "rule" about left-aligning text as column header. I always right-align these if the column is full of numbers. Why? Go and print out those tables when the columns are nice and wide. The mismatch looks ugly, and if wide enough it's not clear what the header text should be lining up with.
@@excelisfun Of course. It's all personal preference and what you need it for. I'm just throwing it out there as an idea for anyone interested. While I have your attention, thank you so much for all the content you put out there. I've learned so much over the years and it definitely helped boost my skills enough to get my MOS Excel Expert certification.
1:27:51: Here Mike you are changing types staticly: = Table.TransformColumnTypes(RenamedFields,{{"Date", type date}, {"Product", type text}, {"Units", Int64.Type}, {"Store", type text}, {"SalesRep", type text}}) This can have a small draw back, if anything changes like column name then the step will crash. It is better to use dynamic column type selection: now, if you would replace change type step in advanced editoor to this: MyStep = Table.TransformColumnTypes(RenamedFields, Table.ToColumns( Table.Transpose( Table.AddColumn( Table.FromList( Table.ColumnNames(RenamedFields)), "ColumnType", each if Text.Contains([Column1], "date") or Text.Contains([Column1], "Date") then type date else if Text.Contains([Column1], "Units") or Text.Contains([Column1], "units") or Text.Contains([Column1], "Amount") or Text.Contains([Column1], "amount") or Text.Contains([Column1], "Value") or Text.Contains([Column1], "value") then type number else type text ) ) ) ) you could prepare yourself for any / all possibilities that could happen also yes, you can take this one step further and validate against list of text value: i.e: stackoverflow.com/questions/53197587/power-query-check-if-string-contains-strings-from-a-list community.powerbi.com/t5/Desktop/How-to-do-the-text-containsany-in-power-query/m-p/932703#M446938 But I think the solution i gave above is ok on begginer's level. I hope this is usefull
Sir one thing I want to ask.. If you please guide.. If I complete your full Microsoft 365 playlist will I able to master Excel, power query and power bi???? And the second question how much time i should give daily because I'm working professional in data entry domain.. Please answer these 2 questions 🙏🙏
No. I provide free classes with lots of free content, but the only way you can master anything is time and herd work ; ) I provide some good detail, but that's it. How much time? The answer is as much as you can ; ) There are no easy answers in becoming awesome, even though most UA-cam videos promise other wise lol
Hi MIke, thx for ur teaching. Just to avoid confusions for future users of that great video. After uploading "San Jose" File the Sales for SalesRep "MIki" are missing in the Excelfile and Power BI Desktopin the video- Hopefully nothing personal :-). Its not critic - just hint...Maybe also u updated the excercise Files.
Thanks for the hint for the Team : ) : ) I pinned it so everyone will know!
Unbelievable, unique, no-comparison on all media channels. This guy not only gives you everything, but the quality is second to none. He enjoys giving to people, to all people. He replies to all, he respects all subscribers. I have had the pleasures, to exchange comment with Mr. Girvin. He enjoys exchanging with us, as much as we enjoy having a reply from him. Great video, as always, Sir. Greetings from Scorpio of Clearwater.
I am so glad that you like this no-comparison on all media channels stuff that I post, Scorpio of Clearwater!!!!!!!
@@excelisfun Yes Sir, you are a Scorpio too, right ? ... 😊😊
@@bucs2021 Yep!!!!! It is way fun to be a Scorpio!!! As you know ; )
I don't how to thank you, there is no word in the dictionary to express how grateful i am for everything you do for us .
You are welcome!!!!
Practice problems are posted at end of Start file. HW problem #6 is a comprehensive practice problem that pulls together all the teachings in the video - not an easy one - great for practice!!
Thank you so much for all you do. You are my favorite teacher ever and I am inspired by your teachings.
I am so happy to inspire you with my teaching : ) : ) your comment inspires me : )
Mike, been following you for YEARS and recommending your channel anytime somebody asks me how to learn Excel. One point where I differ with you: justification of table headers. I justify the column header to match the data beneath the header. So if a column of data is number, then I right justify the header. If the data is text, I left justify the header. It just makes reading easier - especially for wide columns.
I never heard of that before. But it sounds good to me. Especially since my rule for alignment is solely to help track down data type errors. There are actually many exceptions to the rule of numbers right and text left : ) Yours is a good one.
Mike, excellent as always. I love the way you teach best practice on the way through like the no spaces in PQ names etc. Exceptional!
Great video for anyone learning PBI- I fall asleep watching other UA-cam videos this long- your energy/enthusiasm though never lets that happen!!
Yes!!!! No sleeping at the excelisfun channel lol
Thanks!
Thank you for your kind donation : ) : )
Thank you for all you do. It could take forever to figure out all these capabilities with Excel and Power Bi
No, no, you can easily do it in less than forever!!! : )
Basics or not......its always fun to watch and follow. Simply Amazing!!!!
Glad it is so : ) : ) : ) What parts were amazing in this one? Since you are a formula guy ; )
@@excelisfun Conditional Formatting and Power Query and P.I were FUN!!!
@@johnborg5419 No doubt, especially that Power BI visualize part - so much easier than in Excel : )
This video is on another level. I learned a ton from this one. Thank you!!
Yes!!!! Love to hear you made it to the next level!!!!!
You are awesome Mike:)
I am so glad to help : ) : )
I'll be watching this video later today Mike so I can brush up on some of these topics, thanks for all you do for the Team!!!!!
You know most all of this, Teammate Chris M!!!! But I would be interested if there is any knew stuff, or stuff you totally forgot in this lol
I do know most of this (due in large part to watching your videos over the last 4 years and practicing) but I want to brush up on Power BI; this is my weak link and something I want to get better at in 2023!
@@chrism9037 MECS video #4 and this one have my best Power BI stuff. If you know Power Query and DAX, then Power BI is easy. Most Power BI users in the world who think they know Power BI do not know Power Query, M Code and DAX at all, which means they don't know Power BI. The hard stuff is all behind the scenes. The visualizing and sharing part in the easy part. I think this video shows why all us Excel users must use Power BI - it is just MUCH easier to build visuals and dashboards with Power BI : ) : ) : )
Made it through the first section so one thrd finished!!! Have to warch in three parts. I totally agree about Pie charts. My absolute rule on number of pie pieces is: #of slices
That is too funny: My absolute rule on number of pie pieces is: #of slices
1:21:24 here we need to be very vvery carful. as Mike mentioned many times, PQ is case sensitive, meaning xlsx is not equal to Xlsx or XLSX.
Technicaly this means it is a good practice to use one of the transformations: Text.lower, Text.Upper or Capitalize first letter, and only after to filter to designated extension.
Very true. I originally had the example set up to show that, but took it out to streamline the example lol
This is Art! Master painter Mike on the Excel Canvas.✌😆
Art = 1) few extraneous elements and 2) every time you come back, you can see a new truth. That is true throughout all history. Here is an example: SUM((CriteriaArray=Criteria)*Numbers) : ) : ) : ) : ) : ) : ) : )
EXCELLENT presentation. A+++. Thank you.
You are welcome for the AAA : ) : )
Thank you Mike. You know your stuff! 😄
Just trying to have fun ; )
As a person in graphic production in some cases centred text and even right align text is better and clearer to read, I think it just depends on what the text is and the relationship to the column to the right
You are right. For the final reports center is often OK. Mostly solutions with formulas should not have centered numbers - to make tracking errors easier.
1:24:15 here I would use different way:
I would use Table.Transformcolumns() function to change the binary object to excel object instead of adding additional column.
and then I would use a formula to pass Name into each workbook separetly, and finaly i would use Table.combine to combine list of excel tables from Content column.
I work with very hard files so the less columns I generate the easier it is to work with the files.
Great method, Rad Teammate!!
Thanks Mike for this EXCELlent video.
Awesome. Do we have other visualisation tutorial on this channel? Complete dashboard kind of .
This is a class, so you can try this earlier video: ua-cam.com/video/TjSnQ4VDHTE/v-deo.html
Mike, you inspired me to learn everything about Lamba and Let. Here is Bingo Card generator code that I created. Its probably simple, but I felt accomplished seeing it work!
=LET(
name, {"B","I","N","G","O"},
bn, 1,
in, 16,
nn, 31,
gn, 46,
on, 61,
RandGen, LAMBDA(num,TAKE(SORTBY(SEQUENCE(15,,num),RANDARRAY(15)),5)),
B, RandGen(bn),
I, RandGen(in),
N, RandGen(nn),
G, RandGen(gn),
O, RandGen(on),
topN, TAKE(N, 2),
midN, "Free",
bottomN, TAKE(N, -2),
game, HSTACK(B,I,VSTACK(topN,midN,bottomN),G,O),
card, VSTACK(name, game),
card
)
That is a thing of beauty! BAM: for LambdaBam!!!!!! Go Team : )
The best ever!
Glad it helps!!!!
I did something slightly different.
In the Title measure, I didn't create measures for Max and Min dates & referenced them from the measure.
From a DAX Studio perspective, it is probably less efficient as it means recalculating the max & min everytime.
Reaching to 1M subscribers 🎉🥳
Someday : ) : ) : ) : )
Just to double check, Dates are still recognized as serial numbers in power bi like they are in excel correct?
Yes. Here is a translation list of serial numbers that will be in my upcoming M Code book:
Power Query Serial Numbers Power Query Dates Excel Serial Numbers Excel Dates
2958465 12/31/9999 2958465 12/31/9999
45114 7/7/2023 45114 7/7/2023
366 12/31/1900 366 12/31/1900
365 12/30/1900 365 12/30/1900
61 3/1/1900 61 3/1/1900
60 2/29/1900
60 2/28/1900 59 2/28/1900
59 2/27/1900 3 1/3/1900
3 1/2/1900 2 1/2/1900
2 1/1/1900 1 1/1/1900
1 12/31/1899 Text 12/31/1899
0 12/30/1899 Text 12/30/1899
-1 12/29/1899 Text 12/29/1899
-2 12/28/1899 Text 12/28/1899
-102589 2/12/1619 Text 2/12/1619
-255899 5/15/1199 Text 5/15/1199
-644000 10/13/0136 Text 10/13/0136
How do you highlight text in yellow while presenting (software or video editing effect)?
It is all done with editing in Camtasia. A video like this has thousands of edits - that is the hard part of video making, but most important.
According to the article (wiki) , Tufte, has also criticised powerpoint junk. It is good to have some confirmation: I have seen so much ppt junk: people showing off with flying words, crazy transitions, bright yellow writing on light blue. I have never used the stuff, and every time I was told by the show-offs that I should really learn to use powerpoint as I do not even know how to put in a flying sentence. Everybody became a show-off so as not to be told that he cannot use powerpoint. (In the Tufte article, the facts on the Columbia are quite shocking.) - sorry for being a bit off topic -
Ooooo Absolutely: I saw Tufte at a seminar about 2010 and he hammered PowerPoint. He is right. But, PowerPoint is the correct tool to use when you want to help your audience fall asleep... lol I used to have to teach PowerPoint, and I spent one day (since there is really nothing to learn) and I used to show them all the things NOT to do...
1:25:51 that is Fun way of doing it. but also bit harder since you are creating multiple steps.
If we could get updatetable Sales representative List of names, (import from sharepoint list, or another separete workbook, or sharepoint location (...) then we could simply filter the Name.1 column via list of values, and each time the would get updated we could easly update the file.
Thanks for the alternatives!!!!
@@excelisfun I hope you are nod angry with my comments.
@@ExcelInstructor It is the opposite: I am happy when ideas are shared to help the Team! The more eyes on a problem, the bigger the pool of amazing solutions and ideas : )
@@excelisfun Thank you :)
@@ExcelInstructor Go Team!!!
On the ArrayDashboard tab of the Finished file, cell Q9 contains the following formula:
=SUMIFS(S[Revenue],S[Date],">="&Q6,S[Date],"
I do teach in the LAMBDA part of this class. Video 10 : )
Great video, as always. A shorter keyboard shortcut for Conditional Formatting Rules Manager is ALT O, D. It's very nice when there are existing rules to modifiy. Otherwise, it's the same number of steps to get to a new rule.
I can't remember where I saw this, but I use it a lot and it's one less key. I don't know the other legacy keyboard shorcuts. If anyone knows of a compilation of those old shortcuts it would be interesting to see if there are any common ones that are two letter combos
You might have learned it here at excelisfun because I used Alt, O, D for the first 7-8 years at UA-cam. Then I switched becasue I am crazy with keyboards - I measure each one to see when is easier. I use Alt, H, L, N when I create a new rule because if you use Alt, O, D you have to use this full keyboard to get to New: Alt, O, D, Alt + N. Whereas with Alt, H, L, N - you do not have to stop and add a second Alt to go with N. Alt, H, L, N is more sooth for me because I just tap Alt once, then H, L, N in succession. I use Alt, O, D when i already have Conditional Formatting Rules, and that keyboard gets me to list of rules. Hard core. Sometimes I do not know how I remember them all...
There is a compilation: it was in one of Mr Excel's Excel 2007 books: he had a full many-pages-grid of all the old ALTs and all the new ALTs. I can't remember which book, but I do have it on my shelf of 175 Excel books at work...
I just got to work and found the book: Special Edition Using Microsoft Office Excel 2007 by Bill Jelen pages 78 - 85 have a list of all legacy Alt keyboards. This book is the most complete Excel book I have ever seen. It has everything: every function, every menu item, every ribbon item, every keyboard.
I just searched and found the book:
Special Edition Using Microsoft Office Excel 2007
very cheap online ...
@@excelisfun Mike, those are great points. Alt O,D is great for editing existing formats, but I have to concede Alt H, L, ? gets you to the particular option you want.
I'm switching to keyboard shortcuts more and more and after the initial adjustment, especially for navigating around the worksheet. There are other combos I use (Alt-H, Alt-A and Alt-M branches) to where I almost forget where the icons are on the ribbon. For example, I hardly ever use CTRL + V anymore, and instead one of the variants of Alt H, V, ?
I appreciate the way you continue to integrate keyboard shortcuts into your videos.
@@excelisfun Thanks Mike. I will take a look
1:19:22 That is true that all of them are objects that can be vied in PQ when doing the import.
However, when importing to power query in excel, some files have "Magical Object" in it and by magical object i mean a secondary exact same data set, that can be viewed when importing data to power query from excel workbook, however when looking in name manager, file info, and so on this object cannot be found.
however this VBA Code:
Sub DeleteNamedRanges()
Dim MyName As Name
For Each MyName In Names
ActiveWorkbook.Names(MyName.Name).Delete
Next
End Sub
will remove all objects including that "magical object"
this magical object is something generated when exporting datasets from external softwares like SAP ot Tririga or even Trintech.
I do not know if n SAP 4 hana this problem persist.
it is problem in some SAP s3 versions.
Thanks for the rad tip, My Rad Friend!
6:48 sorry but I disagree with a "rule" about left-aligning text as column header. I always right-align these if the column is full of numbers.
Why? Go and print out those tables when the columns are nice and wide. The mismatch looks ugly, and if wide enough it's not clear what the header text should be lining up with.
No worries. That rule is really for tracking data type errors. For final visuals, you can align as you wish.
@@excelisfun Of course. It's all personal preference and what you need it for. I'm just throwing it out there as an idea for anyone interested.
While I have your attention, thank you so much for all the content you put out there. I've learned so much over the years and it definitely helped boost my skills enough to get my MOS Excel Expert certification.
@@Darkslide820 Yes!!!! I love to hear that my posted stuff helps! That is what I have been trying to do here at UA-cam for past 14 years : )
1:27:51:
Here Mike you are changing types staticly:
= Table.TransformColumnTypes(RenamedFields,{{"Date", type date}, {"Product", type text}, {"Units", Int64.Type}, {"Store", type text}, {"SalesRep", type text}})
This can have a small draw back, if anything changes like column name then the step will crash.
It is better to use dynamic column type selection:
now, if you would replace change type step in advanced editoor to this:
MyStep =
Table.TransformColumnTypes(RenamedFields,
Table.ToColumns(
Table.Transpose(
Table.AddColumn(
Table.FromList(
Table.ColumnNames(RenamedFields)), "ColumnType", each
if
Text.Contains([Column1], "date") or
Text.Contains([Column1], "Date")
then type date
else if
Text.Contains([Column1], "Units") or
Text.Contains([Column1], "units") or
Text.Contains([Column1], "Amount") or
Text.Contains([Column1], "amount") or
Text.Contains([Column1], "Value") or
Text.Contains([Column1], "value")
then type number
else type text
)
)
)
)
you could prepare yourself for any / all possibilities that could happen
also yes, you can take this one step further and validate against list of text value: i.e:
stackoverflow.com/questions/53197587/power-query-check-if-string-contains-strings-from-a-list
community.powerbi.com/t5/Desktop/How-to-do-the-text-containsany-in-power-query/m-p/932703#M446938
But I think the solution i gave above is ok on begginer's level.
I hope this is usefull
You are right: the dreaded changed column names... Your solution is very nice, My Rad Friend : ) Thanks for helping the Team with great efficiency!!!
@@excelisfun yes, this technique can be used for both dynamic column name changing and dynamic column type changing.
Sir one thing I want to ask.. If you please guide.. If I complete your full Microsoft 365 playlist will I able to master Excel, power query and power bi???? And the second question how much time i should give daily because I'm working professional in data entry domain.. Please answer these 2 questions 🙏🙏
No. I provide free classes with lots of free content, but the only way you can master anything is time and herd work ; ) I provide some good detail, but that's it. How much time? The answer is as much as you can ; ) There are no easy answers in becoming awesome, even though most UA-cam videos promise other wise lol
First comments
first place trophy for you!!!!
🆒🆒🆒🆒😎😎😎
: ) : ) : )