Dataflows for Power BI with Dynamics GP Data: Part 2

In Part 1 of Dataflows for Power BI with Dynamics GP data, we talked about what dataflows are and started creating our first dataflow entity by creating a Date dimension. Now let’s start creating the remaining entities for:

Fill out your Server, Database and Connection credentials for the Enterprise gateway.

I like to connect to my data via SQL views and you can download the queries for those views here. Once you’re done creating all of the entities your dataflows screen should look something like this.

Creating Custom Functions in Dataflows

The easiest way to create custom function in Power BI service’s dataflow is to create it in Power BI Desktop’s Power Query and then open up the Advance Editor to copy and paste the M code into a blank query.

let

Source = (input) =>

let

values = {

{“ALABAMA”,”AL”},

{“ALASKA”,”AK”},

{“ARIZONA”,”AZ”},

{“ARKANSAS”,”AR”},

{“CALIFORNIA”,”CA”},

{“COLORADO”,”CO”},

{“CONNECTICUT”,”CT”},

{“DELAWARE”,”DE”},

{“FLORIDA”,”FL”},

{“GEORGIA”,”GA”},

{“HAWAII”,”HI”},

{“IDAHO”,”ID”},

{“ILLINOIS”,”IL”},

{“INDIANA”,”IN”},

{“IOWA”,”IA”},

{“KANSAS”,”KS”},

{“KENTUCKY”,”KY”},

{“LOUISIANA”,”LA”},

{“MAINE”,”ME”},

{“MARYLAND”,”MD”},

{“MASSACHUSETTS”,”MA”},

{“MICHIGAN”,”MI”},

{“MINNESOTA”,”MN”},

{“MISSISSIPPI”,”MS”},

{“MISSOURI”,”MO”},

{“MONTANA”,”MT”},

{“NEBRASKA”,”NE”},

{“NEVADA”,”NV”},

{“NEW HAMPSHIRE”,”NH”},

{“NEW JERSEY”,”NJ”},

{“NEW MEXICO”,”NM”},

{“NEW YORK”,”NY”},

{“NORTH CAROLINA”,”NC”},

{“NORTH DAKOTA”,”ND”},

{“OHIO”,”OH”},

{“OKLAHOMA”,”OK”},

{“OREGON”,”OR”},

{“PENNSYLVANIA”,”PA”},

{“RHODE ISLAND”,”RI”},

{“SOUTH CAROLINA”,”SC”},

{“SOUTH DAKOTA”,”SD”},

{“TENNESSEE”,”TN”},

{“TEXAS”,”TX”},

{“UTAH”,”UT”},

{“VERMONT”,”VT”},

{“VIRGINIA”,”VA”},

{“WASHINGTON”,”WA”},

{“WEST VIRGINIA”,”WV”},

{“WISCONSIN”,”WI”},

{“WYOMING”,”WY”},

{“WASHINGTON, D.C.”,”DC”},

{“WAHINGTON”,”WA”},

{“ONTARIO”,”ON”},

{“ONTARIO CANADA”,”ON”},

{“QUEBEC”, “QC”},

{“QUEBEC CANADA”,”QC”},

{“NEWFOUNDLAND”,”NL”},

{“ALBERTA”,”AB”},

{“ALBERTA CANADA”,”AB”},

{“BRITISH COLUMBIA”,”BC”},

{“BRITISH COLUMBIA CANADA”,”BC”},

{“MANITOBA”,”MB”}

},

Result = List.First(List.Select(values, each _{0}=input)){1}

in

Result

in

Source

Rename the custom function to “fnLookup”.

Now we need to modify our Customer entity to clean up the state column. Right click on the Customer entity and select “Advanced Editor”.

Copy and paste the below Power Query code into the Advanced Editor window. This custom function helps replace the data in the State column with the accepted two-character State abbreviations.

After all your dataflow entities have been created, save your changes and create a schedule to refresh your data.

Connecting our Dataflows to Power BI Desktop

From your workstation with the December release of the Power BI Desktop, click the “Get Data” button and select the “Power BI dataflows (Beta)” connection.

Select the dataflow entities we created in the Power BI Service.

Once all of your entities have been imported, navigate to the Relationship section to verify the correct relationships exist and make any changes. Your relationship view should look like the screenshot below.

Don’t forget to mark our dataflow Date entities as a date table after importing it into Power BI Desktop.

I’m going to build out a Product Performance report from the dataflow entities that I created, I will add ABC segmentation, moving averages and time comparisons using DAX measures. ABC segmentation allows you to sort a list of values in three groups, which have different impacts on the final result. ABC segmentation works on the famous Pareto principle, which states that 20% of efforts give 80% of the result. The meaning of the segments: A – the most important for the total of (20% gives 80% of the results). B – average in importance (30% – 15%). C – the least important (50% – 5%).

Are three segments enough? Should we use more? What percentages? To answer these questions, you need to know your data and what the ABC segmentation is being done for. I have seen companies using 4 classes (ABCD) or even more. For this example, three classes have the advantage that they separate the assortment in three categories of high, medium, low importance, which is easy to communicate.

In the Product Performance report below, I used a scatter chart to analyze the Product classes profit margin and total profits, the Sales ABC by ABC Class stacked bar chart segments, the products using ABC segmentation which allows you to drill down into each segment to see the underlying detail. Additionally, there is a total cost by warehouse location and weekly moving average cost comparison chart at the bottom of the report. Here are the DAX measures and DAX Columns that I used to create the Product Performance report:

Ready to Do Even More with Your Data?

Start organizing, knowing and executing on your data today with dataflows and Power BI to provide a self-service data lake in the future. KTL Solutions works with business leaders every day in helping them lead their organization into becoming a data-driven organization. Need help executing on your data? Contact KTL today.