Power BI for Accountants: Complete Introduction to Power BI Desktop to Build Reports & Dashboards

[@kittycat8753]

The videos are great- enthusiastic, Sound is clear, easy to follow. the "table of contents" in the description allows you to jump.

[@h0tie]

3 years as an audience, excelisfun has been giving and continues to do so

[@excelisfun]

I am glad that the videos help. I am not sure what you mean by 3 years/ Do you mean you have been watching for 3 years?

[@AmitSingh-er3lk]

I almost watch your all excel videos, and learn alot from them... now the same way i foun Power Bi too..

[@solomonnzenwa8496]

Im impressed with your procedures

[@excelisfun]

I am glad that the video helps : )

[@RUJedi]

I'm not familiar with even half of the Excel features you mentioned in this video (still much to learn this padawan does), but Power BI looks amazing. Your videos & channel are always spot on, easy to follow, and I love your use of shortcut and formula images to reinforce your vocal instructions. This is one of my favorite instruction channels!

[@excelisfun]

Glad the videos and methods help!!! Thank you for your support with a Sub and comment and Thumbs Up on each video that you watch.

[@RUJedi]

Been a subscriber for a few years now and I watch & like whenever possible. Keep up the great work!

[@jamesstewart1966]

Very good delivery of Power BI organizing the data fields, data topics, and reporting.

[@excelisfun]

Glad you liked it, James! Thank you for your support!

[@mohamedchakroun4973]

Thanks you Mike for this video, I have this trick in creating data calendar from one date to another by trying this: 1-Go to the GET DATA, BLANK QUERY 2-Than Go to the view ribbon and click in "Advanced Editor" 3- Paste this code let fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) as table => let DayCount = Duration.Days(Duration.From(EndDate - StartDate)), Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)), TableFromList = Table.FromList(Source, Splitter.SplitByNothing()), ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}), RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}), InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),type text), InsertYearNumber = Table.AddColumn(RenamedColumns, "YearNumber", each Date.Year([Date])), InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])), InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type text), InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])), InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]), InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM"), type text), InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])), InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])), InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])), InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text), InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date), InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Week Number", each Date.WeekOfYear([Date])), InsertMonthnYear = Table.AddColumn(InsertWeekNumber,"MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100), InsertQuarternYear = Table.AddColumn(InsertMonthnYear,"QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100), ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{"QuarternYear", Int64.Type},{"Week Number", Int64.Type},{"Year", type text},{"MonthnYear", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}, {"DayInWeek", Int64.Type}}), InsertShortYear = Table.AddColumn(ChangedType1, "ShortYear", each Text.End(Text.From([Year]), 2), type text), AddFY = Table.AddColumn(InsertShortYear, "FY", each "FY"&(if [MonthOfYear]>=FYStartMonth then Text.From(Number.From([ShortYear])+1) else [ShortYear])) in AddFY in fnDateTable 4 click done 5 enter your parameter (start date- end Date) and click invoke 6-You will have your calender with a bunch of analytical coloumn

[@excelisfun]

Thanks for the trick.

[@brianxyz]

Mike, you can still put multiple fields into the Rows well. Drag them one below the other into the Rows field well. In the visual up in the left-hand corner you should see three symbols. If you hover your mouse over the last one it should say "Expand all down one level in the hierarchy." This will expand the matrix down to the next level. If you have three levels you can click it again and go down to that level, etc. The second symbol/icon takes you down one level at a time to show only the data for that level. The first symbol/icon lets you drill up. Trust this helps.

[@excelisfun]

Wow, I tired that!?!?!? But it does not show both at once at the Matrix used to do... : (

[@brianxyz]

Mike, it definitely works unless I'm misunderstanding what it is you are trying to do. Is this what you're trying to achieve? Trust the screenshot is clear enough for you to read properly. 1drv.ms/i/s!AuSY9hyhKFqthgco4Y_j1DZCLqls

[@excelisfun]

That is not what I was trying to do. I was trying to do like the opening shot that showed the finish report...

[@maxxouful]

after research, I finally found how to manage properly the categories/sub-cat: on your matrix, click on "format", then expand "row header" and turn off "stepped layout" and you have it ;-) enjoy

[@excelisfun]

Thanks for the tip : )

[@ahmedabulkheir3331]

finally 👏, I hope you do a series for power BI as you did with many other topics from basics to advanced.

[@fabiomorais8643]

Had fun watching this introduction to pbi!

[@excelisfun]

Glad it was fun for you too ; )

[@quangduong5273]

Easy to understand how to use Power BI. Thank you so much for this video.

[@excelisfun]

You are welcome so much!

[@ishaq79]

Amazing, whenever I want learn new things related to Excel, you're my one stop shop.

[@excelisfun]

Glad the videos help! Thanks for your support : )

[@ennykraft]

As always, a great and super helpful tutorial. I love watching your videos even when I'm familiar with the topic because I like the way you teach. Unfortunately I don't have PowerPivot so learning the formulas in Power BI is really helpful for next year when I can change my plan. Btw, MS have recently added conditional formatting to matrix tables which I love since it means I now can use the space of the bar chart for something else. Now if only we could define how to sort that list, I'd be so happy. I solved the problem of not being able to have both the category AND the product name in the matrix by creating a new column in which a new text with the category & product name were combined. Now the list repeats the category over and over which isn't as neat as in a PivotTable but it's better than nothing.

[@vida1719]

For the matric view you requested, drag Category in the Rows field. Category MUST be above Product. Then click the third circle (joined 2 arrows) on the left of the matrix. Or you can use simple Table and drag everything to the Values area. Matrix looks better though. To generate calendar I use Calendarauto() - no argument needed in the brackets. I haven’t tried if it works with future dates. Also, to get other columns quickly, just start typing name of your date table, hit TAB to select it, and you get a list of List of [Month], [MonthNo], [Year], etc. The formula would look like this: Month = dDate[Date].[Month]

[@excelisfun]

Thanks for the tips!

[@jimmyni1983]

Great Introduction @ExcelIsFun. Thank you so much and please keep up great job. Have a nice weekend.

[@excelisfun]

Thank you.

[@unigirl123]

this is so awesome! thank you so so much for creating more contents especially "for Accountants" series

[@excelisfun]

You are welcome! Thank you for your support with comment and Thumbs Up on each video that you watch and a Sub : )

[@rodrigocustodio1955]

Thank you, Mike, you are always so inspirational and always delivering material in a very energic and effective way. Mike, I agree with Ahmed, can you create a Power BI only series?

[@nw9239]

Loved this! You are an awesome teacher and trainer!

[@excelisfun]

Glad you love it! Thanks for your support with comment, Thumbs Up and Sub : )

[@NicholasEricBallard]

Mike, your lessons teach me so much. Every video is so useful. Thank you!

[@excelisfun]

I am glad they are so useful !! Thank you for your support with Thumbs Up and comment and Sub : )

[@chengjinzhao5676]

good teacher forever

[@excelisfun]

Glad you like the teaching, chengjin!!!

[@Soulenergy31]

Endless blessings for you and family Mr. Mike, Great materail as always!!!!!! very much appriciated, Excel-Power BISen Sei!

[@excelisfun]

Glad you like the videos! Thanks for your support with comment, Thumbs up and sub : )

[@De1taX]

Woohoo! Love all your videos Mike!

[@friklloyd2716]

Mike, There are 2 Matrix Visualizations. If you use the top one that reads Matrix and not Matrix preview then you can drag category into the rows area. The other one also works but you have to expand it like brianxyz posted. I may have the older power bi desktop

[@excelisfun]

Thanks!

[@ISelfAcademySnippet]

As usual Mike is great thanks i have learned a lot from you and u always surprise me every time when u introduce a video i will do a podcast oneday like "the Great - Mike"

[@excelisfun]

I am glad that the videos help and are useful : )

[@joseblazquez2178]

Just as with regular Excel videos: Awesome! Thank you so much for taking the time to put this together, God bless you

[@excelisfun]

You are welcome!! Thanks for your support : )

[@fungsweewong3774]

I got an issue of not getting correct matrix display (the year columns won't dsplay) when I drag Product into Rows and Year into Columns. Any help on that?

[@karljolivet5991]

I just love the way you make it fun. Learned a lot again

[@himanshudalai1028]

Fantastic one. Great learning !! Thank you Mike. !!

[@TheConanwarrior]

Great video as always! Would you be able to create a video that shows how to create a P&L & Balance Sheet on Power BI? Thanks

[@mohammadrahman1397]

HiMike, it is a fantastic video to learn PowerBI. my only question is whether we need to create the ddate table or not. can we not use date from allsalestablescombine to create those relationship as I found little complicated to write this dax function. thanks

[@strschoolschool6748]

Great Tutorial Mike. Well done. Please can you help us with the path function? We want to create the income statements with sub totals for total income,gross profit, total expenses, net profit

[@peterclarke2041]

Hi, firstly - great video. However, would anyone be able to help... for some reason my date field isn't working as you show it. The dates pulled down into the column in the table but isn't pulling anything when I add it as a column. Any ideas?

[@restakmee2292]

Hi do i need to learn power query or power pivot before i start learning power bi? How should i start learning, i only have basic excel knowledge

[@excelisfun]

You can check out my accounting playlist: kzbin.info/aero/PLrRPvpgDmw0ndqetqYG0ocKrr-3FvouJ-

[@restakmee2292]

@@excelisfun noted sure. Thanks. However do you think i can jump straight to power bi first?

[@44.7b.kaharaditya6]

Great Sir, for this series

[@excelisfun]

You are welcome, Manoj!!!

[@chaudhryalisalam3668]

simply amazing sir , keep it up

[@kelechiohaeri1435]

Thanks Mr Excelisfun....always delivering value!

[@mathew9665]

Hi Mike, Does the Power BI Desktop & the user license allow you to forward a copy of the generated file to another person in the company? Also I can't see an option to print the sheet, am I missing something here? Thanks for your time

[@kasunmanchanayake5550]

Hi Mike, Thank you very much for the great content always. My QUESTION is can we use "CALENDERAUTO()" function to generate dates instead of this long formula

[@bottonline4544]

Hi Kasun , im Jose Pino From Venezuela. I agree with you the calendar auto option is more easy. But the truth is that much knowledge of DAX is required to write that formula. Mike is too good... Regards

[@ensysconsultores]

Really helpful! Love the way you have created a calendar with dax, very easy and less cryptic than with 'M'. Also, I'd love to see how you replicate the double click drill from pivot tables in power BI. Tx for sharing

[@excelisfun]

Glad it was helpful! I do not know how to double lick like in PivotTable...

[@ensysconsultores]

ExcelIsFun join the team haha, I'm trying with a combination of the new matrix visual with its capabilities for drilling down, mixing it up with hierarchies for some fields, but still getting non optimal results..tx

[@SuperMegamalik]

Excellent Video, You are great man !

[@excelisfun]

Glad I can help, Muhammad!!!!

[@c11725571]

Hi Michael, Thank you for this wonderful video. I have an issue with the date, would you be able to assist, please? When I change the data type from Text to Date it is giving me an error. Thank you again

[@KiidDJ]

it would be cool to do a " how to set up a budget" video :)

[@alexkim7270]

@ExcelIsFun I'm wondering how can I easily transpose the values from rows to columns.

[@shorn77777]

sir, i tried this but, there were two issues, all others were done exactly as you guided. [thank you for making life easier, bringing the DAX, it was so good, the measures i mean] however, i need some guidance, when i pulled the date as month number, and month and year, it did work at first, but when i tried to do the same on the same table by adding an extr. column, it didn't show the format correctly, instead it just pulled the date itself, [[[[[[[ mon=format(tablename[fieldname],"mmm") ]]]]]]. also, although i linked the two tables correctly, when i pulled them it appeared to be not linked.instead of showing data under year, it showed aggregated and under 2015,2016 2017 there were no data. kindly give guidance,

[@michaelmassaro9897]

I had this same problem "also, although i linked the two tables correctly, when i pulled them it appeared to be not linked.instead of showing data under year, it showed aggregated and under 2015,2016 2017". Did you ever get an answer for this?

[@efeotofia1145]

You guys are awesome... Thanks for this useful information as usual....Cheers !!!

[@excelisfun]

You are welcome! Thanks for your support : )

[@alexkim7270]

Mike is there anything in your channel that teaches how to do data modelling for accountants?

[@excelisfun]

You can check this playlist: kzbin.info/aero/PLrRPvpgDmw0ndqetqYG0ocKrr-3FvouJ-

[@anuranjansingh7120]

sort but very useful, i learn many thing from this video ... Thank you @excellsfun

[@jaffarrizvi3447]

U are great sir. Salute to you.

[@strschoolschool6748]

We would also like to create the balance sheet using Power BI, we need your help . Thanks in adavance

[@kittycat8753]

The txt file import was very helpful- esp for finance reports that are txt files generated monthly to a folder (ie diver model reports). Question: If I had file names "ABC_20120101.txt" (File_yearmonthday) for every month of every year going back lets say 10 years; how do I select only the most recent 3 years form the file name ? Thanks

[@shikhiful]

Very cool! You're the best!

[@cpdmartin]

Hi Mike, when I run the report, I get Total values only, I don't seem to be able get the individual products in the report. They are there initially but once I add year and revenue in the report construction, they fall out of the report. Great videa as always, thanks again. CPMartin

[@excelisfun]

I am not sure what is going one. Anyone else know what is causing Products to drop out?

[@cpdmartin]

Hi Mike, it would appear that I used the old "dProduct" table used in the power query model. It did not contain the product 680CAD, that was identified as 600CA - therefore there were a number of fields that were not being picked up and the system failed. Great exercise to solve the problem and understand PowerBI - awesome. Look forward to more material Great video as always - thanks again

[@michaelmassaro9897]

Having same problem. was this ever solved?

[@RUJedi]

So geeking out right now.

[@excelisfun]

Geek is good!

[@ahmedal-dossary4386]

Awesome as always Mike..!!!!!!

[@excelisfun]

Glad it is awesome, Ahmed!!! Thanks for the support!!!

[@zubairso]

When I am applying the Power BI slicer, i keep getting a selection for blanks. Whereas I don't have any blanks in my calendar table.

[@andriipopadiuk2316]

a very solid video!! thanks

[@excelisfun]

Glad it was solid and good for you, Andrii!!!! Thanks for your support : )

[@PlantationChronicles]

How to make the year seperate? mine is not seperate. It all sum into one column

[@brianx4264]

Power BI, love it . keep it coming!

[@excelisfun]

Okay : )

[@rrrprogram8667]

Great Greatttt Greattttttttttt...........Thanks Mikeeee......

[@excelisfun]

You are welcome!

[@MrHarounH]

I get an error when filling in the mmm in the formula how can i deslove this?

[@pushliang7867]

amazing btw where can download the files in this Video ?

[@RahulPatel-nq3or]

Amazing!

[@excelisfun]

Glad you like it! Thank you for your support : )

[@khalikshaikh9273]

you are pretty amazing sir

[@jackieo2936]

La Quinta in Tacoma?!? Shoutout to T-town!! Do you do any in-class training in Tacoma?

[@excelisfun]

T-town!!!!! I have done a number of Accounting Seminars in Tacoma over the years. Nothing planned in the near future.

[@xt.7933]

Starting from 10 I am a little bit confused. Seems I need to watch some DAX videos first?

[@ayeshasamad7416]

Thanks Mike! How do I Download Power BI in Mac?

[@excelisfun]

There is no such things. No Power Query, Power BI, DAX and so on in a Mac, unless you add a windows partition. This is why most entities that do analytics do not use Macs... : ( The sad news is that I started my Excel career in the early 1990s on a Mac, and loved my Mac, but soon got chased off the Mac as I became an Accountant and analyst and the Mac did not have many of the programs that I needed...

[@sandie1712]

Could you pl also release some videos on Cube functions.......Thanks!!

[@malikg4u]

hi. i have question i made work book with help of your formula but now i am suck basically i have added some columns and get my total but i want to show total in bigger NO in may 4 box at same time =IF(ROWS(B$4:B4)

[@excelisfun]

I am not sure. Try posting question to; mrexcel.com/forum

[@pmsocho]

Awesome!

[@excelisfun]

Thanks, Teammate!!!

[@Sal_A]

Is there a way to publish Power BI Desktop onto SharePoint so that everyone can see it in one place? Is it possible? Else you would have to distribute your .pbix file to every individual correct, including whenever I make a change (assuming I'm the originator)?

[@bhahnable]

Sal A I'm admittedly new to Power BI, but what I understand is what you are asking for is the Power BI Services half of Power BI. An online dashboard that you can share with your team. Maybe you can embed this into your SharePoint site, but not sure.

[@Bao_Huynh]

My formula bar doesn't give any suggestion at all. Like when he typed CAL...it give a suggestion for CALCULATION. Found similar issue online with no solution. Anyone know why?

[@xt.7933]

It is interesting that if I type Month=format(dDate[MonthNumber],"mmm"), it will return Dec rather than Jan although the monthnumber is 1. Why?

[@RichardMcMullen]

Clara Xiaoxiao Tang try changing MonthNumber to Date

[@masud.md272]

We need VBA & Macro tutorial in Excell please

[@NoShadowOfDoubt1]

Epic!

[@Elricky124]

I dig it :)

[@excelisfun]

Glad you dig it!

[@brunaiannazzo192]

there is no link below the video

[@excelisfun]

Click "Show More"

[@brunaiannazzo192]

@@excelisfun I actually don't see "show more" -- I am using a Mac... I hate this computer!

[@excelisfun]

@@brunaiannazzo192 Download Files: Zipped Folder file Text Files: people.highline.edu/mgirvin/KZbinExcelIsFun/WAATP2017-Power-BI-Desktop-TextFiles.zip Excel File: people.highline.edu/mgirvin/KZbinExcelIsFun/WAATP2017-Power-BI-Desktop-Example-Start.xlsx Finished File: people.highline.edu/mgirvin/KZbinExcelIsFun/WAAPT2017-PowerBIDesktopExample.pbix

[@excelisfun]

@@brunaiannazzo192 I do not even know if Power BI works on a Mac. I know the parallel tools in Excel do not exist on a Mac. Since about 2010, Microsoft stopped add some of the amazing new tools to Mac Excel.

[@MUSIKANIJAYSON22]

PAAHWER!

[@excelisfun]

Glad you like it! Thanks for the support with your comment, Thumbs Up and Sub : )