Import multiple files from Dropbox folder into PowerBI and Excel (via PowerQuery) at once

Below you’ll find a video where you can see how easy it is to import multiples files from a Dropbox folder into PowerBI or Excel at once.

There are 2 different methods to grant access to your Dropbox: Grant access to the whole Dropbox or to a (newly created ) folder only. I will present the folder-method, as granting access to your whole Dropbox is really dangerous in my eyes – unless you are prepared to share it all publicly: The token generated will allow anyone to read your data. So also all those people who you’ve sent this beautiful dashboard where you just forgot that it contained your token…

To make it super-easy for you, I’ve created a function that you can download here: fnDropboxFolder

The code for it I’ve got from this thread in the PowerBI forum, which contains some additional useful information and a link to a solution with a custom connector for PowerBI, making it easy to deploy in a corporate environment (designed by Igor Cotruta).

Just watch how it works:

Some screenshots to follow along:

Read more

Unpivot by number of columns and rows in PowerBI and PowerQuery in Excel

Today I’m sharing with you one of my killer M(inja)-strikes: Unpivot a table by simply passing the number of columns that shall remain (at the left side of the table) and the number of rows (who hold the header-information) as parameters to them.

This is not only incredibly flexible (multiple header rows), but also very robust: You don’t have to care about changing column names for future refreshed or when you apply it to partitioned tables where the partitions itself have different column names already.

Unpivot by numbers

here: 3,2 (number of columns, number of rows):

returns:

Flexible and robust:

Your table has to be prepared as follows:

  • The columns that shall NOT be unpivoted must stand on the left side of your table. Their number must match the 2nd parameter you feed into the function (“FirstNColumnsToKeep”)
  • The header rows that shall be unpivoted must sit in the first rows of your table. So one of them still sit as the header row itself, you have to demote it (Home -> Transform -> dropdown at “Use First Row as Headers” -> “Use Headers as first rows”)

M-Code

Read more

Bill of Materials Implosion or “Where used Analysis” in Excel and Power BI

This is the last part of my little series about bill-of-materials (BOM) calculations made easy with M in Power BI and Power Query: The implosion that shows where each part is used:

BOM: “Where used?”

BOM Where used analysis

 

When we look closely at the table “BOMReport” from the previous post, we see that the data is already there: “PathExplode” shows that the component “HL Spindle/Axis” (1) is used in the TopParentProduct “Road 150 Red, 62” (2) via some intermediate products (3):

BOMReport holds that information already

 

And if we filter on the example from the pivot-table from above, we can spot that pattern as well:

All we have to do is to create one row for each “Where-used” item. But as we have columns for the compenent itself already as well as for the TopParentProduct, we actually only want to create additional rows for the intermediate products.

Therefore we could split the column “PathExplode” up into its components. But that’s not necessary because it has been generated from the lists in column “PathItem”. So we just have to eliminate the first and the last item from those lists and expand them:

M-Code to extract the intermediate parts:

M-code to extract and expand the intermediate products

M-Code as text: BOM_ImplosionCalc1.txt

Subscribers can download the Excel-file:   BOM_ImplosionCalc1.xlsx

Enjoy & stay queryious 🙂

Bill of Material (BOM) Explosion Part2: Costing in Excel and PowerBI

Following up on the BOM-explosion: A comment reminded me that I had missed to present the costing techniques to calculate the total costs of each (sub)-product.

Reversing the aggregation direction

What I had shown is how to “aggregate” from parent down to child-level to retrieve the total quantity of each component within a BOM (“How many of each (sub-) components do we have to order (or build) for that bike?”) (1).

Now we reverse the aggregation direction and aggregate the total (!) quantities back up to the parents (2).

And, as this doesn’t make too much sense in an economical way, the second aggregation will be their prices (3). This will give us the sum of all part-costs (“How much will the order of all the parts cost us?”). This is also very useful for planning purposes or reconciliation of prices for intermediate products with your master data.

And if your model holds sales-data as well, you can calculate the totals costs of your total sales within each period. (4)

VAR 1: Using classical hierarchies

Read more

Pivot your table-relationships in Power BI and Power Pivot

While the relationships view of the datamodel provides a very good overview which tables are connected to each other, one cannot see at a glance on which field they are connected to each other.

This is where a pivot table-view of the field-connections can be really helpful:

Pivot-table-view:

Table-Fields-Connections in Pivot-View

 

Here you see the tables on the many-side in the rows and in the columns are the tables on the one-side (of course you can change that). Add some slicers if your model is very large.

Read more