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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
jessicarocha
Helper IV
Helper IV

Is it possible to add new rows in the dataset based in a codition?

The customer can order some personalised product. When we acept the order, there is a commitment to order every year a minimum amount. I would like to check which customers stick to the agreement. 

 

So I created a metric to say how much is missing to reach the target (Order Qty - Target), for example. 

However, in some situations (Material 0002 and 0003)  no quantity was ordered in a specific business year. In these cases, there is no target. The row is not created. 

Thus, I would like to create a row based in a condition.
After the creation date, it should always contain a value for all business years following. In case there is not, create a new row whrere the values are copied from the previous row, except the Order Qty column has 0 as a value.

Current Table:

Material Creation Date Order Qty Target Business Year
0001 01.08.2019 100 150 2019/2020
0001 01.08.2019 150 150 2020/2021
0001 01.08.2019 300 150 2021/2022
0002 01.09.2020 590 500 2020/2021
0003 01.08.2018 900 1000 2018/2019
0003 01.08.2018 1300 1000 2020/2021


Want to add (what is in bold):

Material Creation Date Order Qty Target Business Year
0001 01.08.2019 100 150 2019/2020
0001 01.08.2019 150 150 2020/2021
0001 01.08.2019 300 150 2021/2022
0002 01.09.2020 590 500 2020/2021
0002 01.09.2020 0 500 2021/2022
0003 01.08.2018 900 1000 2018/2019
0003 01.08.2018 0 1000 2019/2020
0003 01.08.2018 1300 1000 2020/2021
0003 01.08.2018 0 1000 2021/2022


Is it possible to do that?

I think that an index column in the business year would help, but I have no idea how to do this.

 

Business year goes from 01.07.xxxx to 01.07.xxxx+1   -> Business year (xxxx/xxxx+1)

 

Thanks a lot for the help!

1 ACCEPTED SOLUTION
v-kkf-msft
Community Support
Community Support

Hi @jessicarocha ,

 

Please try the following code:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc1RCsAgDAPQu/RbNKkI9izi/a+x1bmhMH8CKY+0NQFACQJG1Kig3YWAZ/H0U1IopIeDLqtWuOZR521b6VpfrVNbHB+DFBuJ3+28bte72LONyVnTeHrizLv/5vsF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Material = _t, #"Creation Date" = _t, #"Order Qty" = _t, Target = _t, #"Business Year" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Material", Int64.Type}, {"Creation Date", type date}, {"Order Qty", Int64.Type}, {"Target", Int64.Type}, {"Business Year", type text}})
in
    #"Changed Type"
let
    Source = Table.SelectColumns(Table,"Material"),
    #"Removed Duplicates" = Table.Distinct(Source),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", each Table.AddIndexColumn(Table.Sort(Table.Distinct(Table.SelectColumns(Table,"Business Year")),{"Business Year"}), "Index", 1, 1, Int64.Type)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Business Year", "Index"}, {"Custom.Business Year", "Custom.Index"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Custom", {"Material", "Custom.Business Year"}, Table, {"Material", "Business Year"}, "Table", JoinKind.LeftOuter),
    #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"Creation Date", "Order Qty", "Target"}, {"Table.Creation Date", "Table.Order Qty", "Table.Target"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Table",{"Material","Custom.Index"}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Material"}, {{"All_Rows", each Table.FillDown(_,{"Table.Creation Date","Table.Target"}), type table}}),
    #"Expanded All_Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All_Rows", {"Custom.Business Year", "Custom.Index", "Table.Creation Date", "Table.Order Qty", "Table.Target"}, {"All_Rows.Custom.Business Year", "All_Rows.Custom.Index", "All_Rows.Table.Creation Date", "All_Rows.Table.Order Qty", "All_Rows.Table.Target"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded All_Rows", each ([All_Rows.Table.Target] <> null)),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",null,0,Replacer.ReplaceValue,{"All_Rows.Table.Order Qty"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"All_Rows.Custom.Business Year", "Business Year"}, {"All_Rows.Custom.Index", "Index"}, {"All_Rows.Table.Creation Date", "Creation Date"}, {"All_Rows.Table.Order Qty", "Order Qty"}, {"All_Rows.Table.Target", "Target"}})
in
    #"Renamed Columns"

vkkfmsft_1-1633401398039.png

vkkfmsft_2-1633401415591.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

8 REPLIES 8
v-kkf-msft
Community Support
Community Support

Hi @jessicarocha ,

 

Please try the following code:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc1RCsAgDAPQu/RbNKkI9izi/a+x1bmhMH8CKY+0NQFACQJG1Kig3YWAZ/H0U1IopIeDLqtWuOZR521b6VpfrVNbHB+DFBuJ3+28bte72LONyVnTeHrizLv/5vsF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Material = _t, #"Creation Date" = _t, #"Order Qty" = _t, Target = _t, #"Business Year" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Material", Int64.Type}, {"Creation Date", type date}, {"Order Qty", Int64.Type}, {"Target", Int64.Type}, {"Business Year", type text}})
in
    #"Changed Type"
let
    Source = Table.SelectColumns(Table,"Material"),
    #"Removed Duplicates" = Table.Distinct(Source),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", each Table.AddIndexColumn(Table.Sort(Table.Distinct(Table.SelectColumns(Table,"Business Year")),{"Business Year"}), "Index", 1, 1, Int64.Type)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Business Year", "Index"}, {"Custom.Business Year", "Custom.Index"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Custom", {"Material", "Custom.Business Year"}, Table, {"Material", "Business Year"}, "Table", JoinKind.LeftOuter),
    #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"Creation Date", "Order Qty", "Target"}, {"Table.Creation Date", "Table.Order Qty", "Table.Target"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Table",{"Material","Custom.Index"}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Material"}, {{"All_Rows", each Table.FillDown(_,{"Table.Creation Date","Table.Target"}), type table}}),
    #"Expanded All_Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All_Rows", {"Custom.Business Year", "Custom.Index", "Table.Creation Date", "Table.Order Qty", "Table.Target"}, {"All_Rows.Custom.Business Year", "All_Rows.Custom.Index", "All_Rows.Table.Creation Date", "All_Rows.Table.Order Qty", "All_Rows.Table.Target"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded All_Rows", each ([All_Rows.Table.Target] <> null)),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",null,0,Replacer.ReplaceValue,{"All_Rows.Table.Order Qty"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"All_Rows.Custom.Business Year", "Business Year"}, {"All_Rows.Custom.Index", "Index"}, {"All_Rows.Table.Creation Date", "Creation Date"}, {"All_Rows.Table.Order Qty", "Order Qty"}, {"All_Rows.Table.Target", "Target"}})
in
    #"Renamed Columns"

vkkfmsft_1-1633401398039.png

vkkfmsft_2-1633401415591.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hi @v-kkf-msft. Thank you so much for your help! That is exactly what I need.

It does run a bit slow because my dataset is big. So I will make a request for this problem to be changed in the database directly but until then, thanks to you, I get to run my report. 

I really appreciate it! 

Kind regards, 

Jéssica

mattww
Responsive Resident
Responsive Resident

I wouldn't approach this by adding dummy data into your dataset, it seems more like a data modelling problem.

 

The Business Year is best held in a separate calendar table, then you have access to all the years regardless of whether there are actual order details for that year. 

 

When creating a measure for order quantity, you can do something like below, where the +0 would force a value of zero where there are no records.

 

 =SUM([OrderQty]]) + 0

@mattww I do have a dimension only with the business year. But if in the fact table that business year does not appear (like for product 0003 there is no entry for fiscal year 2019/2020), I can not force the zero, right?

I could do a cartesian product and add all fiscal years in the fact table and then replace the nulls with zeros. But this would be wrong because I would add all fiscal years available in the dimension fiscal year. Thus, I would also have fiscal years in the fact table before the product was actually created. And in this cases, it would be wrong to have a 0 there....

 

jessicarocha_0-1632980404174.png

 

Hi @jessicarocha , you should be able to force a zero, see sample linked below.

 

https://easyupload.io/sqdgzm

The outstanding bit is how to get your target so it would be useful to understand how this number is generated, for example is it an annual target, does it depend on the customer/product?

 

I would think a second fact table with your targets, linked to the relevant dimensions would probably do the trick, rather than storing the target on your transactional fact table

Hi @mattww ,

I can't open your link due to some security layer of where I work. I will try later at home.
The new fact table seems like a good idea. You understood it correctly, the target does depend of the customer and the product.

However, I still don't see how this will solve the missing fiscal years in the transactional fact table...

Ah sorry about that, unfortunately I can't upload .pbix files on here (I think it's only available at a certain rank). If you continue to struggle to get it, direct message me and can send it directly.

In the meantime, here are some screenshots which might better illustrate what I mean

 

So for your second Fact table, you would probably include a DateKey for the start of the fiscal year the target relates to, as well as a CustomerKey and Product Key. As your Actual and Target Facts would share the same Date, Customer and Product dimensions, this should allow you to combine the two facts in the same visuals.

 

Cal1.PNGCal2.PNGCal3.PNG

@mattww thank you so much for the screenshots. 

I followed your explanation step by step since I can't open the file. I did everything like the screenshots. 

The problem is, that by doing this, I have 0s for all Fiscal Years available in my Calendar Dimension. 

 

For example:
Product 003 was created in 2018/2019. I do get a 0 for 2020/2021 and 2021/2022. That is correct. 

However, in the case of product 002, it is not correct.  This product was created in 2020/2021 year, so I should only get 0 for the future years, like 2021/2022. Instead, I also get 0 for year 2018/2019 and 2019/2020 when the product still did not exist. In this case, it should be null and not 0...

 

Do you know what I mean?

 

Thanks again.

Kind regards, 

Jéssica

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.