Power bi cheat sheet macaw

0% found this document useful (0 votes)

136 views2 pages

Macaw Power BI Cheat Sheet EN

Original Title:

Macaw_Power_BI_cheat_sheet_EN

Jump to Page

You are on page 1of 2

You're Reading a Free Preview
Page 2 is not shown in this preview.

Reward Your Curiosity

Everything you want to read.

Anytime. Anywhere. Any device.

No Commitment. Cancel anytime.

Tips & Tricks•Give every step an explanatory name and merge steps of the same type, for better manageability. •Give queries and columns user-friendly names (this is also necessary to utilize Q&A). •Make sure that each column in each table has the correct data type. This will make the data model smaller and (as a result) faster. •Remove columns you are not going to use in your report. Prefer ‘Remove Other Columns’ above the ‘Remove Columns’option, for lower risk that structure changes in your data source break the query. •Maximize the use of Query Folding for faster and more efficient queries. With Query Folding, multiple transformations are merged as one query and then sent to the source. If ‘View Native Query’ is not available, Query Folding has stopped before that step. •Structure your queries in folders. For example: Facts, Dimensions, Functions, Misc. •You can copy and paste Queries easily between files. Dependencies will be included (parameters/queries/functions), and Data Sources will be automatically listed. •Turn off ‘Enable Load’ for queries/tables that you don’t need in the Data Model. •Prefer “reference” over “duplicate” of aquery, •Re-use Power Query code and lower impact on your data source by using Power BI dataflows. •Turn on the Formula Bar so you get familiar with Power Query (M) code. M is an easy way to quickly make small adjustments. •In general, prefer “Import”over “DirectQuery”. Unless the amount of data is too large to import, or when there are other requirements (like real-time insights).•Did you know you can transform your data by using Python and R in the Power BI Query Editor?Code examples (don’t forget thatPower Query / M is case-sensitive!)•ifT > 0 thenA elseB •tryA/B otherwise0 •#table({ “X”,“Y”}, { { 1, 2 }, { 3, 4 } } )•DateTime.LocalNow()•Date.From(DateTime.LocalNow() )•Excel.Workbook(Web.Contents("[url]/[filename].xlsx"), null, true)Resources•Power Query M Formula Reference: .•Notepad++ Power Query support by Lars Schreiber: . Tips & Tricks•Always use a separate Date table in your data model. Mark it as a Date Table. •Only use DAX Calculated Columns when it’s not possible to create it using Power Query. This improves clarity and manageability of your report as transformations are located where you expect them.

Power BI Cheat SheetnewData ModelPower QueryNew: otherlanguagesavailable!English version - October 2019(PDF download)(Contribute to the cheatsheet)I’VE GOT THE POWERBITips & Tricks1.Always use a separate Date table in your data model. Mark it as a Date Table.2.Only use DAX Calculated Columns when it’s not possible to create it using PowerQuery. This improves clarity and manageability of your report as transformationsare located where you expect them. It also improves query speed of the model andreduces refresh duration.3.Give measures a prefix (%, #, €).4.Use abbreviations like YTD, LY, PY, PP as a suffix, to keep the base fields together inthe sort order.5.Hide columns that are needed but are irrelevant for the user.6.Hide the key at the many side of a many-to-one relation (e.g. [OrderDate] in the‘Revenue’ table).7.For each measure column in your data model, make a DAX Calculated Measureinstead of using the ‘Default Summarization’, then hide the original column. Thisway all measures will have the same icon. And it enables you to easily change thecalculation in the future (e.g. adding a filter condition). Also, it is easier toreference this measure in other DAX calculations.8.Always use the table name when you refer to a column, for example:‘Product’[Category].9.Use DIVIDE() to prevent division by 0, and to improve the speed of your divisions.10.Use IsInScope to get the right hierarchy level in DAX (read all about it in Kasper deJonge’s blog: ).11.In DAX: (un)comment DAX lines by pressing Alt + Shift + A or CTRL + /,and Shift+ Enter for line breaks.12.Use aggregations to keep your model small and performant, and still have alldetailed data available.13.Use Tabular Editor to make changes to your Power BI file (currently unsupported byMicrosoft). Also, make sure to check-out its best-practices analyzer.14.Avoid bi-directional cross filtering and make use of measure filters.15.For very large models, group measures or fields in display folders for betterusability.16.Use DAX Studio to capture all DAX queries executed on your Premium Capacity.17.Keep your PBI desktop file fast and small by using TOP N() and switch underlying data source in PBIservice after publishing ().

Can Power BI handle 1 millions of records?

What are the 3 important Power BI tools?

Is Power BI difficult to learn?

What is abc123 in Power BI?