Tutorials

• Functions
  • CUBEVALUE – get directly values from Power Pivot data model
  • Extract text after the last instance of some character
  • How to merge texts in Excel
  • IF – one IF nested in another IF
  • IF function in detail
  • IFS – select from multiple options
  • Merging of selected strings – something like CONCATENATEIFS, if it existed
  • NOW – automatically updated time
  • Percentages and their calculation in Excel
  • The FILTER function using something like “IN” operator – selection of items presented in some list
  • VLOOKUP – function to connect two tables
    • Comparison of two lists and finding differences
• Macros and VBA
  • Macros – quick start
• Office Script tutorials
  • Office Script – how to record script very simply
  • Office Script microtutorial – for loop
  • Office Script microtutorial – get a value of variable from cell
  • Office Script microtutorial – how to write value into a cell
  • Office Script microtutorial – if / else condition
  • Office Script microtutorial – show value in a console
  • Office Script microtutorials – copy / paste cell values
  • Office Script providing values to Power automate flow
  • Value from Power Apps into a Excel cell – using Power Automate and Office Script
• Other tutorials
  • AutoSave to cloud in Excel
  • Power Pivot is nearly in all versions of Excel
  • Saving to One Drive versus saving to Sharpoint
  • Scenarios in Excel
  • Simple Excel calculation of market basket analysis, I mean cross sell / up sell, I mean if someone buys A, will he buy B?
• Pivot tables
  • Pivot tables – quick start
  • Set default layout of pivot tables
  • Simple Gantt chart in Excel
  • Values ​​/ measures in an Excel pivot table below each other instead of next to each other
• Power Automate
  • A simple predictive AI Builder model in Power Automate
  • Mass mailing in Power Automate – sending with attachments
  • Power Automate – flow checking tomorrow´s weather and, if nice, asking for holiday
  • Power Automate – save the Power BI visualization into a Excel file
  • Power Automate triggered from Power BI alert – automatic order when value below threshold
  • Running of Microsoft Flow by cell phone button
  • The “Parse JSON” action in Power Automate and why is it so important
• Power BI
  • Power BI – use cases
    • Comparison of a plan and a reality in Power BI
  • Power Pivot
  • Power Query – Get and Transform
    • Conditional column in Power Query and in Power Pivot queries
    • Connect Excel to Facebook
    • Connect Power BI to WooCommerce, quickly and free
    • Copying of Power Query queries
    • Date modifications with Power Query
    • Evaluation of Google Analytics data in the light of another business data
    • How to merge content of all files in folder with Power Query (Get and Transform)
    • Lists in Power Query
    • Merge data from all sheets in selected file
    • Merging of data from all sheets from files in some folder
    • Power Query – get GPS info for places, using Google API
    • Power Query – get the distance between two places using Google API
    • Power Query – source reference as filepath in cell
    • Power Query – source with relative path
    • Power Query M language – basic principles
    • Query folding and Native query – very simple explanation
    • Retrieving data from a folder – workbooks with different sheet names
    • Text modifications with Power Query (Get and Transform)
    • Types of merging of queries in Power Query or in Power BI
    • Use Power Query as a Pivot Table datasource
  • Power Query M language
    • Conditions in Power Query M language
    • Custom function basic syntax in M language (Power Query)
    • Excel, which downloads and merges all data from the specified folder
    • Keep sorted table for Group By, using Table.Buffer
    • List.Accumulate and column calculations
    • List.Transform – converts list items
    • Merging of all columns in Power Query – regardless of their names
    • Merging of two ROWs in Power Query
    • Power Query – how to find a nth smallest or largest value in table or group
    • Power Query – how to reference ALL columns in a table? The Table.ColumnNames function
    • Power Query and referencing sheet with variable name
    • Rename of first (or n-th) column in Power Query
    • Running totals in Power Query, using the List.Range function
    • The List.Range function, extracting values from specified rows in a column
    • The Table.ColumnNames function and its practical use
    • Value from Excel cell directly to Power Query
  • Power View
    • Bye bye, Power View. I´ll not miss you.
  • Powerbi.com and Power BI Desktop
    • Data Model in Power BI
      • Cross filtering in Power BI – simple explanation
      • Dimensions and fact tables – what is it and how to work with them in Power BI
      • Groups in Power BI
      • Hierarchies in Power BI
      • How to create Dataflow and how to connect to it
      • Many to Many (N:N) relationship in Power BI
      • New table (View) in Data Model
      • Power BI – Column From Example
      • Quick Measures in Power BI
    • DAX – query language for Power BI and Power Pivot
      • % of parent row in Power BI
      • ALLEXCEPT – removes all filter contexts except one (DAX – Power Pivot, Power BI)
      • CALCULATE – “merging” of other functions (DAX – Power Pivot, Power BI)
      • Calculation Groups – easy calculations with multiple measures at the same time
      • CALENDARAUTO – create the time dimension automatically (DAX – Power Pivot, Power BI)
      • CONCATENATEX (DAX – Power Pivot, Power BI)
      • CONTAINSSTRING, CONTAINSSTRINGEXACT – finds text string in another text string (DAX – Power Pivot, Power BI)
      • CROSSJOIN – all combinations of two lists (DAX – Power Pivot, Power BI)
      • DATATABLE – create your data directly in Data Model with no connection (DAX – Power Pivot, Power BI)
      • DATEADD – values for some period shifted in time by a year, quarter or day (DAX – Power Pivot, Power BI)
      • DATEDIFF – difference between two dates or times in units you need
      • DATESMTD, DATESQTD, DATESYTD – dates from the beginning of month / quarter / year (DAX – Power Pivot, Power BI)
      • DAX – comparison of a specific row with all rows of the table
      • DISTINCT (DAX – Power Pivot, Power BI)
      • DIVIDE – division handling zero (DAX – Power Pivot, Power BI)
      • EXCEPT – comparison of tables – finding of additional rows (DAX – Power Pivot, Power BI)
      • FILTER – filters in DAX (DAX – Power Pivot, Power BI)
      • FILTER vs KEEPFILTERS vs VALUES – short comparison
      • Fundamental difference between DATEADD a PARALLELPERIOD functions (DAX – Power Pivot, Power BI)
      • GROUPBY – aggregations in data model using DAX (DAX – Power Pivot, Power BI)
      • How not to spoil your Power BI data model – 7 critical rules
      • IF – simple condition (DAX – Power Pivot, Power BI)
      • IN – operator recognizing presence in a list of values (DAX – Power Pivot, Power BI)
      • INTERSECT – finding of common rows in two tables (DAX – Power Pivot, Power BI)
      • ISFILTERED and removing of subtotals in visualizations
      • ISFILTERED vs ISINSCOPE – basic difference
      • LOOKUPVALUE – assigning of values from other table without relation (DAX – Power Pivot, Power BI)
      • MIN, MAX – highest and smallest values in range (DAX – Power Pivot and Power BI)
      • NAMEOF -function to get name of column (DAX – Power Pivot, Power BI)
      • NATURALINNERJOIN – creates new table from common rows of two tables
      • NATURALLEFTOUTERJOIN – new table using left outer join (DAX – Power Pivot, Power BI)
      • NETWORKDAYS – number of workdays between two dates (DAX – Power Pivot, Power BI)
      • NEXTDAY, NEXTMONTH, NEXTQUARTER, NEXTYEAR, PREVIOUSDAY, PREVIOUSMONTH, PREVIOUSQUARTER, PREVIOUSYEAR – values from previous or next time period (DAX – Power Pivot, Power BI)
      • Number of unique rows – by multiple columns
      • OFFSET – value from previous, next… row in visual (DAX – Power Pivot, Power BI)
      • PARALLELPERIOD – travelling in time (DAX – Power Pivot, Power BI)
      • PATH, PATHLENGTH, PATHITEM, PATHITEMREVERSE, PATHCONTAINS – playing whith hierarchies (DAX – Power Pivot, Power BI)
      • Pearson correlation coefficient in Power BI
      • Playing with SAMEPERIODLASTYEAR and TOTALYTD functions
      • RANK.EQ – determine the order of value in some column (DAX – Power Pivot, Power BI)
      • RELATED (DAX – Power Pivot, Power BI)
      • RELATEDTABLE – assigning of table connected by relation (DAX – Power Pivot, Power BI)
      • ROUND, ROUNDUP, ROUNDDOWN, MROUND – rounding functions (DAX – Power Pivot, Power BI)
      • Running totals simply (DAX – Power Pivot, Power BI)
      • SAMEPERIODLASTYEAR – comparison with previous year (DAX – Power Pivot, Power BI)
      • SELECTCOLUMNS – select some columns from table (DAX – Power Pivot, Power BI)
      • SUMMARIZE – groupping in data models (DAX – Power Pivot, Power BI)
      • SUMMARIZECOLUMNS – grouping in data model (DAX – Power Pivot, Power BI)
      • SUMX – summary with filter or additional calculation (DAX – Power Pivot, Power BI)
      • SUMX vs SUM – key differences very briefly (DAX – Power Pivot, Power BI)
      • SWITCH for simple formulas with multiple conditions
      • TOPN – first N rows from table (DAX – Power Pivot, Power BI)
      • TOTALYTD, TOTALQTD, TOTALMTD – cumulative subtotals in time (DAX – PowerPivot, Power BI)
      • TREATAS – simple calculation using “non existent” relations (DAX – Power Pivot, Power BI)
      • UNION – appending of multiple tables in DAX (DAX – Power Pivot, Power BI)
      • Use of SAMEPERIODLASTYEAR (or similar DAX functions) together with filters and slicers
      • USERPRINCIPALNAME – show users name and use it in RLS (DAX – Power Pivot, Power BI)
      • Variables in DAX
      • Visuals title, dynamically changed by slicer selection
    • Power BI – first steps
      • FAQ – basic life questions in Power BI (and responses, of course…)
      • My first report in Power BI
      • Power BI vs Excel – comparison and main differences
      • Scheme of Microsoft BI both in Excel and outside Excel
      • What is what – Power Query, Power Pivot, Power View
    • Power BI – publishing
      • Pipeline – how to publish Power BI reports simply
      • Publishing from Powerbi.com to any webpage
      • Quick publishing from Excel to Powerbi.com and analysis of Powerbi.com data in Excel
      • Sharing in Power BI – how to share Report, Dataset or Dataflow
    • Power BI charts and visualization
      • Bookmarks and links in Power BI
      • Button “Remove all filters on this page”
      • Conditional formatting in Power BI
      • Custom color schemes in Power BI
      • Custom color schemes in Power BI
      • Custom visuals for Power BI
      • DAX and Power BI – conditional formatting for specific row / column only
      • Decision trees in Power BI
      • DRILLTHROUGH filtering in Power BI – short tutorial
      • Field parameters and how to use them in Power BI
      • Fishes – the funniest visual in Power BI
      • Flow schemes in map
      • Formats and custom formats in Power BI (percent, currencies, thousands separator, decimals…)
      • Gantt chart (project schedule or something like this) in Power BI
      • How to add PowerApps to Power BI dashboard
      • Key influencers – a very smart visual in Power BI
      • KPI visual v Power BI
      • Moving Scatter plot / Scatter chart in Power BI
      • Navigation button working based on RLS
      • Parameters in Power BI visuals
      • Percents and decimals in visual – Power BI
      • Pivot Tables in Power BI
      • Power BI – adding comments in visuals
      • Power BI in phone
      • Pulse chart – beautiful visual for time series presentation
      • Quick switching of different measures in visual
      • R script in Power BI visuals – super simple example
      • Real-time Power BI dashboard
      • Remove filter in visuals
      • Show date and time of data refresh
      • Show top N items in visual
      • Synoptic Panel – create working visual from your custom scheme
      • Usage metrics in Powerbi.com
    • Power Query in Power BI
      • Addressing rows, columns and values in Power BI
      • Connect Power BI to a webpage
      • Connect Power BI with Google Analytics
      • Connect Power Query to a whole SharePoint folder
      • Data quality and distribution check in queries
      • Fuzzy matching in Power BI queries
      • Group by to merge texts – something like CONCATENATEX, but in queries
      • How to schedule updates in Power BI
      • How to simply connect Power BI to Excel file stored on SharePoint
      • How to user Power BI to monitor users activities in Office 365.
      • Incremental refresh in Power BI very briefly
      • Merging of data from all files in folder in Power BI
      • Merging of queries based on date or number intervals – Table.SelectRows function
      • Multiple columns as a key for merging in Power Query
      • Parameters in Power BI queries
      • PDF as a source for Power BI
      • Pivot Column in Power Query
      • Power BI – writing of data directly in Power BI Desktop
      • Power BI connected to Dynamics 365
      • Power Query – how to simply get hyperlinks from webpages
      • Power Query and assigning to ranges (merging of tables like VLOOKUP with last argument 1/TRUE))
      • Python in Power BI – how does it work
      • Remove the typing errors automatically (Query editor in Power BI)
      • Value from previous row – Power Query, M language
    • Show source rows in Power BI
    • Tabular Editor
      • Automatical checking and improving of Data Model with Tabular Editor
      • First use of Tabular Editor in Power BI
• PowerApps
  • Basic PowerApps application
    • Power Apps – working form with basic buttons
    • Power Apps responsiveness – how to make it work
  • Basic use of variables in PowerApps
  • Collections in Power Apps
    • Add and show Power Apps collection
    • Display collections in app
    • Export – save data from collection
  • Compass – PowerApps function – info about cellphones heading
  • Distinct – unique values in Power Apps
  • IsBlankError – very simple verification of correctly filled forms
  • Notify function in Power Apps – simple notification window for user
  • Power Apps – comparing the speed of writing to Excel, Sharepoint and Dataverse
  • Power Apps – how to replace a text input with a dropdown, step by step
  • Power Apps – simple reuse of code
  • Power Apps – useful apps examples
    • Power Apps – navigate to another page after barcode scan
    • Simple editable table, modifying data source, in Power Apps
  • Power Apps – working with calendar dates without hours
  • Power Apps -make AI recognize object very simply
  • Radiobutton getting items itesm table
  • UpdateContext – change controls properties using “something like” variable
• R script
  • Computing correlation in R
  • Correlation plots in R
  • For cycles in R language
  • Getting data from Excel to R-script, using Windows clipboard
  • Matrices in R language
  • R-script as a data source in Power BI Desktop
  • The simplest linear regression in R
  • Use R language to modify query in Power BI
• SAP Analytics Cloud
  • My first SAP Analytics Cloud report
• Statistics and more complex data analysis
  • Correlation – what is correlation and what does correlation coefficient mean
  • Hypothesis testing
  • Linear regression in Excel