Merge queries in Excel using Power Query

Power BI Tip

2024-11-22

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

2024-10-25

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:

var tbl=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,[Sales by blue products],desc)

Result DAX code:

Final DAX expression: Top2ProductsBlue = 
 var tbl=SUMMARIZE( 
 FILTER(TotalSales,TotalSales[Color]="Blue"),
 TotalSales[Products], 
 "Sales by blue products", 
 SUM(TotalSales[Amount]) 
 ) 
 return TOPN(2, tbl, [Sales by blue products], desc)

Day Power BI Tip

2024-08-17

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 = VAR CATEGORIESLIST=VALUES(TotalSales[Products])
 RETURN
 CALCULATE(
    [TotalSales],
    TOPN(2,ALL(TotalSales[Products]),[TotalSales]),
    CATEGORIESLIST
 )

Result measure:

Result in the Report view:

Day Power BI Tip

2023-11-11

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

Example code:     
#"Pivoted Column" = Table.Pivot(#"Your Source",
 List.Distinct(#"Your Source"[Target Column]), 
 "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]);