Skip to content
Trainings, consultancy, tutorialsTrainings, consultancy, tutorials
Trainings, consultancy, tutorials
Understand it right now!
  • Home
  • For corporations
  • Individually
  • Consultancy
  • References
  • Pricing
  • Tutorials
  • Contact
  • Where
  • EN
    • CZ
    • SK
    • EN

  • Registrace
  • Přihlášení
 0
  • No products in the cart.

Subtotal: 0 Kč

View Cart Checkout

 
  • Home
  • For corporations
  • Individually
  • Consultancy
  • References
  • Pricing
  • Tutorials
  • Contact
  • Where
  • EN
    • CZ
    • SK
    • EN

Tutorials

You are here:
  1. Home
  2. Tutorials

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
    • VLOOKUP – function to connect two tables
      • Comparison of two lists and finding differences
  • Macros and VBA
    • Macros – quick start
  • Office Script tutorials
    • 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
  • 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 triggered from Power BI alert – automatic order when value below threshold
    • Running of Microsoft Flow by cell phone button
  • 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
      • 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 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)
        • 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
        • 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
        • 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 – useful apps examples
      • 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
    • Linear regression in Excel

Did you know we have a LinkedIn page?
For companies
Interested in company training?
Excel trainings from beginners to experts. And other Microsoft Office applications as well.
MORE INFORMATION
Individually
  • Consultancy for complex spreadsheets creation
  • Preparation for your new job
  • Preparation for job interview
MORE INFORMATION
Spreadsheet development
VBA - Power BI
  • Development of corporate databases
  • Reports, visualizations, plans, pricelists, budgets...
  • Merging of databases, cleaning, exports
MORE INFORMATION
Recent Comments
  • Dan Paul on SUMMARIZE – groupping in data models (DAX – Power Pivot, Power BI)
  • Haider on LOOKUPVALUE – assigning of values from other table without relation (DAX – Power Pivot, Power BI)
  • namereunused on Remove filter in visuals
  • Anonymous on SUMX vs SUM – key differences very briefly (DAX – Power Pivot, Power BI)
  • jo on SELECTCOLUMNS – select some columns from table (DAX – Power Pivot, Power BI)

ExcelTown - Contact

+420 602 274 999
info@Exceltown.com

Hot news

  • Mass mailing in Power Automate – sending with attachments
  • Office Script microtutorial – for loop
  • Value from Power Apps into a Excel cell – using Power Automate and Office Script

© exceltown.com / 2020 Vyrobilo studio bARTvisions s.r.o.