Question on ETL in PowerBI Desktop

[Anonymous]

Hello,

In my PowerBI model I have a table with distributors' stocks:

My problem is that some distributors (DB) give stock only once a month and some can provide stock on weekly\daily basis. This results in the following pivot table

What I need is: populate stocks in the original table for each DB for each SKU for each day available in the table from the latest previous available date but not earlier than 3 months old. The resulting pivot should look like this (populated cells highlighted yellow):

Thank you.

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You may create a date table.And then create a measure like below.For further,please check the attached file.

Measure = 
VAR _maxdate =
    CALCULATE (
        MAX ( 'Sample'[Date] ),
        FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] < MAX ( 'Date'[Date] ) )
    )
RETURN
    IF (
        ISBLANK ( SUM ( 'Sample'[Stock] ) ),
        CALCULATE (
            SUM ( 'Sample'[Stock] ),
            FILTER ( ALL ( 'Date' ), 'Date'[Date] = _maxdate )
        ),
        SUM ( 'Sample'[Stock] ) 
    )

Regards,

Cherie

[v-cherch-msft]

Hi @Anonymous 

Have you checked my solution before?Please let me know if the measure could help you.

Regards,

Cherie

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You may create a date table.And then create a measure like below.For further,please check the attached file.

Measure = 
VAR _maxdate =
    CALCULATE (
        MAX ( 'Sample'[Date] ),
        FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] < MAX ( 'Date'[Date] ) )
    )
RETURN
    IF (
        ISBLANK ( SUM ( 'Sample'[Stock] ) ),
        CALCULATE (
            SUM ( 'Sample'[Stock] ),
            FILTER ( ALL ( 'Date' ), 'Date'[Date] = _maxdate )
        ),
        SUM ( 'Sample'[Stock] ) 
    )

Regards,

Cherie

[Zubair_Muhammad · Accepted solution]

@Anonymous 

Please check this M/Power Query Solution
File attached as well

It adds missing dates and last non blank stock for each distributor and SKU

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZG7CsQgFET/xTrIfaiJ7bJdymWrkP//jU1AFscxlcg53IGZ4wjvl4YlfPbv/ViJYtFE6/XRcC4dt5Ebch+5N24P9xNyup+R0/2CPI18bdwf8jfklF8bT0/9CAp0QIcG70c0iv8F44pRcO4YhTSJSL2QJxEglEkECCvviMLGQ6JQeUkQTHgqFJS3QsF4LMm94DwWCleT5w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Distributor = _t, SKU = _t, Date = _t, Stock = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Distributor", type text}, {"SKU", type text}, {"Date", type date}, {"Stock", Int64.Type}}),
    SortedRows = Table.Sort(#"Changed Type",{{"Distributor", Order.Ascending}, {"SKU", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(SortedRows, {"Distributor", "SKU"}, {{"Date", each List.Distinct(SortedRows[Date])
, type list}}),
    ExpandedDates = Table.ExpandListColumn(#"Grouped Rows", "Date"),
    #"Merged Queries" = Table.NestedJoin(ExpandedDates,{"Distributor", "SKU", "Date"},SortedRows,{"Distributor", "SKU", "Date"},"ExpandedDates",JoinKind.LeftOuter),
    #"Expanded ExpandedDates" = Table.ExpandTableColumn(#"Merged Queries", "ExpandedDates", {"Stock"}, {"Stock"}),
    #"Added Custom" = Table.AddColumn(#"Expanded ExpandedDates", "Custom", each if [Stock]=null then 
let dist=[Distributor],
SKU_=[SKU], 
mydate=[Date]
in
Table.Max(
Table.SelectRows(#"Expanded ExpandedDates",each [Distributor]=dist and [SKU]=SKU_ and [Date]<mydate and [Stock]>0)
,
"Date")[Stock]
else
[Stock]),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Distributor", Order.Ascending}, {"SKU", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"Custom", "StockQty"}})
in
    #"Renamed Columns"

[Anonymous]

Wow! Thanks a lot! This looks super cool, yet a bit complicated for me.

From what I understand, this is the code I need to type in the advanced editor part of the Power Query tool in MS PowerBI. Would it be possible to do step-by-step guide on how to work this out?

[Zubair_Muhammad]

Sure..

I will do it when i get back in few hours

[Zubair_Muhammad]

@Anonymous 

My apologies for late reply

Here are steps

1) First step is to Group the Table by Columns "Distibutor and SKU" and add a list of each possible date for Distributor/SKU Combination.
2) Once we have all the dates for a Distributor/SKU Combination, we can merge it back to original query and get the missing dates.
3) Next we add a custom column to get the last stock balance for each Distributor/SKU Combination and for each date that was added

You click on the steps in Query Editor to see them in action

[Zubair_Muhammad]

@Anonymous 

Could you copy paste data from first image?

[Anonymous]

Please find sample factStock table below

Distributor	SKU	Date	Stock
DB1	SKU1	26.02.2019	1
DB1	SKU2	26.02.2019	2
DB1	SKU3	26.02.2019	3
DB2	SKU1	26.02.2019	4
DB2	SKU2	26.02.2019	5
DB2	SKU3	26.02.2019	6
DB2	SKU4	26.02.2019	7
DB3	SKU1	26.02.2019	8
DB3	SKU2	26.02.2019	9
DB4	SKU1	26.02.2019	10
DB4	SKU2	26.02.2019	11
DB1	SKU1	01.03.2019	12
DB1	SKU2	01.03.2019	13
DB1	SKU3	01.03.2019	14
DB1	SKU1	04.03.2019	15
DB1	SKU2	04.03.2019	16
DB1	SKU3	04.03.2019	17
DB2	SKU1	04.03.2019	18
DB2	SKU2	04.03.2019	19
DB2	SKU3	04.03.2019	20
DB3	SKU1	04.03.2019	21
DB3	SKU2	04.03.2019	22
DB4	SKU1	05.03.2019	23
DB4	SKU2	05.03.2019	24