let me know if what follows is close to your goal:
query Product:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjZQ0lEKMDAwBFKJiUAiGcQyMtM3MNc3MjAyUIrVASoyhCgyAlJJSSBFIJaBkb6BBaYiYxSTcCgyAckn41JkBFFkCrcOZKaBJVZFZnDr0BXFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Week = _t, Product = _t, Brand = _t, Category = _t, #"Upload date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Week", Int64.Type}, {"Product", type text}, {"Brand", type text}, {"Category", type text}, {"Upload date", type date}},"it-IT"),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Qty", each List.Sum(Table.SelectRows(sales, (r)=> (r[wk]=[Week] and r[Product]=[Product] ))[QTY])),
#"Added Custom 1" = Table.AddColumn(#"Added Custom", "rev", each List.Sum(Table.SelectRows(sales, (r)=> (r[wk]=[Week] and r[Product]=[Product] ))[Revenue]))
in #"Added Custom 1"
query sales:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjAwMFTSUQJjAwMgaWSmb2Cub2RgZKAUqwOXNwJhiLw5NnkTEAbLGxjrG1igyBtD9RuD5Q0N0OXNoPabokvHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, QTY = _t, Revenue = _t, #"Sale Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{"Sale Date", type date}),
#"Changed Type2" = Table.TransformColumnTypes(#"Changed Type",{{"QTY", Int64.Type}, {"Revenue", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type2", "wk", each Date.WeekOfYear([Sale Date],2)),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"wk", Int64.Type}})
in
#"Changed Type1"
with the added auxiliar column wk
The wk column was "forcibly" obtained from the "sale dates" column in order to have the same result for 7/26 and 7/27, which could be two days of different weeks, according to other conventions.
e questo è il risultato:
Recycling Lists: 50 rows a week
let
Source = Folder.Files("\\mytheresa.local\Files\Public\MERCHANDISING RETAIL\RECYCLING\KW\Recycling Lists"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Recycling Lists", each #"Transform File from Recycling Lists"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Recycling Lists"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Recycling Lists", Table.ColumnNames(#"Transform File from Recycling Lists"(#"Sample File"))),
#"Added Custom" = Table.AddColumn(#"Expanded Table Column1", "Week", each Date.WeekOfYear([Latest Recycling Date])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Source.Name"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"No.", "No"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Core Item", type text}, {"Brand", type text}, {"No", type text}, {"Main Waregroup", type text}, {"Item Category", type text}, {"Latest Recycling Date", type date}, {"Week", Int64.Type}}),
#"Added Custom1" = Table.AggregateTableColumn (Table.AddColumn (#"Changed Type", "QR", each Table.SelectRows (DOR, (r) => (r [Date] = [Latest Recycling Date] and r [No] = [No])) [[QTY], [Revenue Net]]), "QR", {{"QTY", List.Sum, "sumQ"}, {"Revenue", List.Sum, "sumR"}} )
in
#"Added Custom1"
Recycling Lists: 300 rows a day
let
Source = Folder.Files("\\mytheresa.local\Files\Public\MERCHANDISING RETAIL\RECYCLING\KW\DOR"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from DOR", each #"Transform File from DOR"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from DOR"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from DOR", Table.ColumnNames(#"Transform File from DOR"(#"Sample File (2)"))),
#"Added Custom" = Table.AddColumn(#"Expanded Table Column1", "Date", each Text.Range([Source.Name],0,8)),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name", "Style", "Item Brand", "Main Waregroup", "Department Group", "Onlineset", "For Sale Date", "Season", "Is Core Item", "Is Exclusive", "Colour", "sale price", "Qty Discount", "Revenue Discount"})
in
#"Removed Columns"
a different way, grouping the sales table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjAwMFTSUQJjAwMgaWSmb2Cub2RgZKAUqwOXNwJhiLw5NnkTEAbLGxjrG1igyBtD9RuD5Q0N0OXNoPabokvHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, QTY = _t, Revenue = _t, #"Sale Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Sale Date", type date},{"QTY", Int64.Type}, {"Revenue", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "wk", each Date.WeekOfYear([Sale Date],2), type number),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Product","wk"}, {{"all", each _, type table [Product=text, QTY=nullable number, Revenue=nullable number, Sale Date=nullable date]}})
in
#"Grouped Rows"
then doing research on subgroups should be faster
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc1BDoAgDATAv/RMwlIQ9RfeCQfg/3+wDUqU6KXbpJNtSuRBhg7ASZQio+nG0WK1DAZlI8h1xBK1KtINbLE9EHfkX01uQldT0Hu70U/TMt5pJ/ZPFMe7GeUT", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Week = _t, Product = _t, Brand = _t, Category = _t, #"Upload date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Week", Int64.Type}, {"Product", type text}, {"Brand", type text}, {"Category", type text}, {"Upload date", type date}},"it-IT"),
ac1= Table.ExpandListColumn(Table.AddColumn( #"Changed Type", "QR", each List.Select(Table.SelectRows(salesGrp, (r)=> (r[Product]=[Product]))[all], (l)=>l[wk]{0}=[Week] )),"QR"),
tac= Table.AggregateTableColumn(ac1, "QR",{{"QTY",List.Sum,"sumQ"},{"Revenue",List.Sum,"sumR"}})
in
tac
but to test and possibly improve the speed of execution we need the details (only the structur and sizes, of course)of the real dataset you are working on
peraphs a merge could be more performant:
let
Source = Table.NestedJoin(productm, {"Product", "Week"}, sales, {"Product", "wk"}, "product.1", JoinKind.FullOuter),
#"Aggregated product.1" = Table.AggregateTableColumn(Source, "product.1", {{"QTY", List.Sum, "Sum of product.1.QTY"}, {"Revenue", List.Sum, "Sum of product.1.Revenue"}})
in
#"Aggregated product.1"
this is the same sales table
HI @LorenzoMolteni ,
Try creating a relationshipp between your 2 tables on Product No.
Thanks,
Pragati
Hi @edhans
Thank you! Unfortunately another problem is occurring right now, meaning that I'm not able anymore to build the relationship between my two sources (products lists and sales numbers). It remains inactive due to other relationships between sale and date.
Please see the screenshoot. I don't know how to solve this...
(Take everything you see here below as a try)
Hi @edhans
Thanks for your reply!
First of all, no need for the Items tabe. That was just a try I was giving to solve the issue.
As you just said, there should be 3 tables in Power Pivot:
- Products list: This is a selection of products that gets uploaded and goes on sale. I have a new selection/list once a week (uploading on Sunday). I get this from Power Query (import from folder). This contains the Product No., Brand, Pre-shopping date (date of upload), Week and now the Fake Date you suggested to create. I want to study the sales on these products on their first week only being live (i.e. if I filter for CW16 I only want to see sale performence for the products I uploaded in CW16 during their first week - not taking into account sales data from CW17 on)
- Sale: This is basically the daily order report for all products we sell. I also get this from Power Query (import from folder). We have here different data, but the ones we need to definetely have are Product No., QTY, Revenue, Date (sale date).
Date: This is simply the date table you suggested to create with Date and Year/Week.
Given the above, I assume I have to create a connection between:
- Product List & Sales on Product No.
- Date & Sales on Date
- Product List & Date on Fake Date / Date
I may have misunderstood your first advices, but not sure of that. How shall I overcome the issue?
Please find attached how the final report looks like. My report occurs when filtering for more/different weeks.
Thanks so much!
Lorenzo
Why not create a true/false field in the product table that determines if this is the first week? Date.IsInCurrentWeek() will do it based on today (refresh date) but you can tweak that or calculate your own week number in there. I don't think there is a need to relate that to the date table specifially. Then just add a filter in your measures for this field.
New Sales =
CALCULATE(
SUM(Sales),
FILTER(
Sales,
RELATED(Product[IsCurrentWeek]) = TRUE()
)
)I typed that in free hand. Didn't test it at all, but you get the idea of what I am considering here.
Helpful resources
Join our Fabric User Panel
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Power BI Monthly Update - June 2025
Check out the June 2025 Power BI update to learn about new features.
Fabric Community Update - June 2025
Find out what's new and trending in the Fabric community.
