Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Question on ETL in PowerBI Desktop

Hello,

 

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

pic1.jpg

 

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

pic2.jpg

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):

pic3.jpg

 

Thank you.

 

2 ACCEPTED SOLUTIONS
v-cherch-msft
Employee
Employee

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

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

@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

 


ETL.png

 

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"

 


 


Regards
Zubair

Please try my custom visuals

View solution in original post

8 REPLIES 8
v-cherch-msft
Employee
Employee

Hi @Anonymous 

 

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

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-cherch-msft
Employee
Employee

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

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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

 


ETL.png

 

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"

 


 


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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?


Sure..

I will do it when i get back in few hours


Regards
Zubair

Please try my custom visuals

@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

 


Regards
Zubair

Please try my custom visuals
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous 

 

Could you copy paste data from first image?


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

Please find sample factStock table below

 

Distributor

SKUDateStock
DB1SKU126.02.20191
DB1SKU226.02.20192
DB1SKU326.02.20193
DB2SKU126.02.20194
DB2SKU226.02.20195
DB2SKU326.02.20196
DB2SKU426.02.20197
DB3SKU126.02.20198
DB3SKU226.02.20199
DB4SKU126.02.201910
DB4SKU226.02.201911
DB1SKU101.03.201912
DB1SKU201.03.201913
DB1SKU301.03.201914
DB1SKU104.03.201915
DB1SKU204.03.201916
DB1SKU304.03.201917
DB2SKU104.03.201918
DB2SKU204.03.201919
DB2SKU304.03.201920
DB3SKU104.03.201921
DB3SKU204.03.201922
DB4SKU105.03.201923
DB4SKU205.03.201924

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.