Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello everyone,
I met some issues with matching two tables. In my table1 I have products listed during a time period with their corresponding status and category. In my table2 I have ProcessStepValues acording the category and status, i.e. if a product from category A moves from one day to the other from status Open to On Hold it should receive the value 40. Here are the tables:
Table1:
Date | Product | Category | Status | |
02.03.2024 | Shoes | A | Open | |
03.03.2024 | Shoes | A | Open | |
04.03.2024 | Shoes | A | On Hold | |
05.03.2024 | Shoes | A | Open | |
03.03.2024 | Book | B | Open | |
04.03.2024 | Book | B | Closed | |
05.03.2024 | Book | B | Closed | |
04.03.2024 | Earings | C | Open | |
05.03.2024 | Earings | C | On Hold | |
06.03.2024 | Earings | C | Closed |
Table2:
Category | Status | ProcessStepValues |
A | Open | 40 |
A | On Hold | 60 |
A | Closed | 0 |
B | Open | 30 |
B | On Hold | 70 |
B | Closed | 0 |
C | Open | 55 |
C | On Hold | 45 |
C | Closed | 0 |
Date | Product | Category | Status | ProcessStepValues |
02.03.2024 | Shoes | A | Open | 0 |
03.03.2024 | Shoes | A | Open | 0 |
04.03.2024 | Shoes | A | On Hold | 40 |
05.03.2024 | Shoes | A | Open | -40 |
03.03.2024 | Book | B | Open | 0 |
04.03.2024 | Book | B | Closed | 100 |
05.03.2024 | Book | B | Closed | 0 |
04.03.2024 | Earings | C | Open | 0 |
05.03.2024 | Earings | C | On Hold | 55 |
06.03.2024 | Earings | C | Closed | 45 |
Everytime I try sth out I get a cycle problem, could someone help me please?
Thank you in advance!
Solved! Go to Solution.
Hi @Ekaterina_, there are many ifs in this query so I'm not sure about speed if you have bigger dataset, byt give it a try 🙂
Result
let
fnProcessStepValues =
(tbl as table) as table =>
let
SortByDate = Table.Sort(tbl, {{"Date", Order.Ascending}}),
BufferSelectedColumns = Table.Buffer(Table.SelectColumns(SortByDate,{"Category", "Status", "ProcessStepValues", "Index"})),
Lg_ProcessStepValues = List.Generate(
()=> [ x = 0, y = 0 ],
each [x] < Table.RowCount(BufferSelectedColumns),
each [ x = [x]+1,
y = if BufferSelectedColumns{x}[Status] = BufferSelectedColumns{[x]}[Status] //Same status
then 0 else
if BufferSelectedColumns{x}[Status] = "Closed" and BufferSelectedColumns{[x]}[Status] = "Open" //Actual row [Status] = "Closed", Prev row [Status] = "Open"
then List.Sum(Table.SelectRows(ChangedTypeTrim_Table2, (a)=> a[Category] = BufferSelectedColumns{x}[Category])[ProcessStepValues]) else
if BufferSelectedColumns{x}[Status] = "Open" and BufferSelectedColumns{[x]}[Status] = "Closed" //Actual row [Status] = "Open", Prev row [Status] = "Closed"
then -List.Sum(Table.SelectRows(ChangedTypeTrim_Table2, (a)=> a[Category] = BufferSelectedColumns{x}[Category])[ProcessStepValues]) else
if BufferSelectedColumns{x}[Index] > BufferSelectedColumns{[x]}[Index] //Actual row [Index] is greater than Prev row [Index]
then BufferSelectedColumns{[x]}[ProcessStepValues] else -BufferSelectedColumns{x}[ProcessStepValues] ],
each [y]
),
RemovedColumns = Table.RemoveColumns(tbl, {"ProcessStepValues", "Index"}),
Merged = Table.FromColumns(Table.ToColumns(RemovedColumns) & {Lg_ProcessStepValues}, Value.Type(RemovedColumns & #table(type table[ProcessStepValues=number],{})))
in
Merged,
Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDSMzDWMzIwMlFQ0lEKzshPLQbSjkDsX5Cap6AUqwNUY0yEGhOcavIUPPJzUiCqTEm1zSk/PxtMQ9VgsQykBKrCOSe/OBWbVTjUoJjjmliUmZcOcpAzqoNM8ahC9pwZTB1CmQJUHczSWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Product = _t, Category = _t, Status = _t]),
Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfIvSM1TANImBkqxOlChPAWP/JwUkKgZQtQ5J784FSwIEXNC0myMJATRDGSZIwTR9Toj6TU1RQghLDZBiEI0Q/TGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Status = _t, ProcessStepValues = _t]),
ChangedTypeTrim_Table1 = Table.TransformColumns(Table1,{{"Date", each Date.From(_), type date}} & List.Transform({"Product", "Category", "Status"}, (colName)=> { colName, Text.Trim, type text })),
// Buffered
ChangedTypeTrim_Table2 = Table.Buffer(Table.TransformColumns(Table2,{{"ProcessStepValues", each Number.From(_), type number}} & List.Transform({"Category", "Status"}, (colName)=> { colName, Text.Trim, type text }))),
Table2_AddedIndex = Table.AddIndexColumn(ChangedTypeTrim_Table2, "Index", 0, 1, Int64.Type),
MergedQueries = Table.NestedJoin(ChangedTypeTrim_Table1, {"Category", "Status"}, Table2_AddedIndex, {"Category", "Status"}, "Table2", JoinKind.LeftOuter),
ExpandedTable2 = Table.ExpandTableColumn(MergedQueries, "Table2", {"ProcessStepValues", "Index"}, {"ProcessStepValues", "Index"}),
GroupedRows = Table.Group(ExpandedTable2, {"Product", "Category"}, {{"All", each fnProcessStepValues(_), type table}}),
CominedAll = Table.Combine(GroupedRows[All])
in
CominedAll
Hi @Ekaterina_, there are many ifs in this query so I'm not sure about speed if you have bigger dataset, byt give it a try 🙂
Result
let
fnProcessStepValues =
(tbl as table) as table =>
let
SortByDate = Table.Sort(tbl, {{"Date", Order.Ascending}}),
BufferSelectedColumns = Table.Buffer(Table.SelectColumns(SortByDate,{"Category", "Status", "ProcessStepValues", "Index"})),
Lg_ProcessStepValues = List.Generate(
()=> [ x = 0, y = 0 ],
each [x] < Table.RowCount(BufferSelectedColumns),
each [ x = [x]+1,
y = if BufferSelectedColumns{x}[Status] = BufferSelectedColumns{[x]}[Status] //Same status
then 0 else
if BufferSelectedColumns{x}[Status] = "Closed" and BufferSelectedColumns{[x]}[Status] = "Open" //Actual row [Status] = "Closed", Prev row [Status] = "Open"
then List.Sum(Table.SelectRows(ChangedTypeTrim_Table2, (a)=> a[Category] = BufferSelectedColumns{x}[Category])[ProcessStepValues]) else
if BufferSelectedColumns{x}[Status] = "Open" and BufferSelectedColumns{[x]}[Status] = "Closed" //Actual row [Status] = "Open", Prev row [Status] = "Closed"
then -List.Sum(Table.SelectRows(ChangedTypeTrim_Table2, (a)=> a[Category] = BufferSelectedColumns{x}[Category])[ProcessStepValues]) else
if BufferSelectedColumns{x}[Index] > BufferSelectedColumns{[x]}[Index] //Actual row [Index] is greater than Prev row [Index]
then BufferSelectedColumns{[x]}[ProcessStepValues] else -BufferSelectedColumns{x}[ProcessStepValues] ],
each [y]
),
RemovedColumns = Table.RemoveColumns(tbl, {"ProcessStepValues", "Index"}),
Merged = Table.FromColumns(Table.ToColumns(RemovedColumns) & {Lg_ProcessStepValues}, Value.Type(RemovedColumns & #table(type table[ProcessStepValues=number],{})))
in
Merged,
Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDSMzDWMzIwMlFQ0lEKzshPLQbSjkDsX5Cap6AUqwNUY0yEGhOcavIUPPJzUiCqTEm1zSk/PxtMQ9VgsQykBKrCOSe/OBWbVTjUoJjjmliUmZcOcpAzqoNM8ahC9pwZTB1CmQJUHczSWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Product = _t, Category = _t, Status = _t]),
Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfIvSM1TANImBkqxOlChPAWP/JwUkKgZQtQ5J784FSwIEXNC0myMJATRDGSZIwTR9Toj6TU1RQghLDZBiEI0Q/TGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Status = _t, ProcessStepValues = _t]),
ChangedTypeTrim_Table1 = Table.TransformColumns(Table1,{{"Date", each Date.From(_), type date}} & List.Transform({"Product", "Category", "Status"}, (colName)=> { colName, Text.Trim, type text })),
// Buffered
ChangedTypeTrim_Table2 = Table.Buffer(Table.TransformColumns(Table2,{{"ProcessStepValues", each Number.From(_), type number}} & List.Transform({"Category", "Status"}, (colName)=> { colName, Text.Trim, type text }))),
Table2_AddedIndex = Table.AddIndexColumn(ChangedTypeTrim_Table2, "Index", 0, 1, Int64.Type),
MergedQueries = Table.NestedJoin(ChangedTypeTrim_Table1, {"Category", "Status"}, Table2_AddedIndex, {"Category", "Status"}, "Table2", JoinKind.LeftOuter),
ExpandedTable2 = Table.ExpandTableColumn(MergedQueries, "Table2", {"ProcessStepValues", "Index"}, {"ProcessStepValues", "Index"}),
GroupedRows = Table.Group(ExpandedTable2, {"Product", "Category"}, {{"All", each fnProcessStepValues(_), type table}}),
CominedAll = Table.Combine(GroupedRows[All])
in
CominedAll
Wow, amazing. Thank you very much!