Power BI Tip

Dear readers, in this example we show how to select specific columns using Power Query. We have the TotalSales table with two columns.

If we want to add one column that specifies manager’s name from the existing data source we should to add this field in the scope like in this example:

Our new code:

#"Removed Other Columns"=Table.SelectColumns(#"Changed Type",{"Products","Amount","Manager"})

If we need to add another addition column with null values before manager’s name for future data that we do not have right now the query will be:

=Table.SelectColumns(#"Changed Type",{"Products","Amount","Blank Column","Manager"},MissingField.UseNull)

Result code:

Power BI Tip

Dear readers, in this example we show how to summarize data by products applying specific filter and then to preset the data as top 2 products table in the model.

First you should create a variable. The variable will contain our temp table:

vartbl=SUMMARIZE(FILTER(TotalSales,TotalSales[Color]="Blue"),TotalSales[Products],"Sales by blue products",SUM(TotalSales[Amount]))

Comments to the DAX expression: you apply filter to the TotalSales table by Color column FILTER(TotalSales,TotalSales[Color]="Blue"), then to choose the group column TotalSales[Products] and create a new column "Sales by blue products" which will contain an amount of total sales of the product.

Second step to return the table with only top two products in descending order:

TOPN(2,tbl,[Salesbyblueproducts],desc)

Result DAX code, Final DAX expression:

Top2ProductsBlue=vartbl=SUMMARIZE(FILTER(TotalSales,TotalSales[Color]="Blue"),TotalSales[Products],"Sales by blue products",SUM(TotalSales[Amount]))returnTOPN(2,tbl,[Salesbyblueproducts],desc)

Day Power BI Tip

In this example we show how to summarize values by categories based on total amount of the ratio for each category. In other words, we are going to find TOP N categories based on total amount each category in the whole scope.

For our purpose we should to create two measures:

First measure to calculate Sales amount by Products:

TotalSales=SUM(TotalSales[Amount])

Second measure to calculate total amount of sales for top two Products, example code measure 2:

Top2Products=VARCATEGORIESLIST=VALUES(TotalSales[Products])RETURNCALCULATE([TotalSales],TOPN(2,ALL(TotalSales[Products]),[TotalSales]),CATEGORIESLIST)

Result measure:

Result in the Report view:

Day Power BI Tip

In this example we show how to use a specific column for heads in your pivot table.

Examplecode:#"Pivoted Column"=Table.Pivot(#"Your Source",List.Distinct(#"Your Source"[TargetColumn]),"Target Column","Name of column of values")

Pivot table:

You could select values for your pivot table heads from:

• other query (QueryName[Target Column]);
• another previous step of your query (NameOfQueryStep[Target Column]);