Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Dear Guru's
I have been searching for the answer, came close, but each person's needs are slightly different.
I have what seems like a simple task, but alas, I need help.
Below is an illustration of what I am dealing with. I have three tables
1. Purchasing Data - Contains purchasing data of customer store transactions
2. Survey Data - Contains survey data of customer survey trends and feedback
3. Final Result - Contains Customer ID, requires the last transaction with corresponding data elements from both purchasing and survey tables.
I hope the below explains. Note: I would prefer to create this in power query mode.
Solved! Go to Solution.
Hi @Anno2019
You could add a grouped index onto your purchase and survey table, which could then be used as a filter to identify the most recent entry per customer. See sample code below:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS1SMFTSUTIy1Tcw1jcyMAJxgkvyi1KBwrE6SKqMQKrM9Q1M8KoCSRgCzTJFVWWEqQpkowWqKmNMGw2N9A0MUVWZKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer ID" = _t, #"Purchase Date" = _t, #"Store Name" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer ID", type text}, {"Purchase Date", type date}, {"Store Name", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Purchase Date", Order.Descending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Customer ID"}, {{"Count", each _, type table [Customer ID=nullable text, Purchase Date=nullable date, Store Name=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Purchase Index",1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Customer ID", "Purchase Date", "Store Name", "Purchase Index"}, {"Customer ID.1", "Purchase Date", "Store Name", "Purchase Index"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"Customer ID", "Purchase Date", "Store Name", "Purchase Index"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Purchase Index] = 1))
in
#"Filtered Rows"
This is based on the following blog post if you want to work through the steps yourself.
Create Row Number for Each Group in Power BI using Power Query - RADACAD
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anno2019,
Did mattww 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept his suggestions to help others who faced similar requirements.
If that also doesn't help, please share more detailed information to help us clarify your scenario to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi @Anno2019
You could add a grouped index onto your purchase and survey table, which could then be used as a filter to identify the most recent entry per customer. See sample code below:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS1SMFTSUTIy1Tcw1jcyMAJxgkvyi1KBwrE6SKqMQKrM9Q1M8KoCSRgCzTJFVWWEqQpkowWqKmNMGw2N9A0MUVWZKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer ID" = _t, #"Purchase Date" = _t, #"Store Name" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer ID", type text}, {"Purchase Date", type date}, {"Store Name", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Purchase Date", Order.Descending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Customer ID"}, {{"Count", each _, type table [Customer ID=nullable text, Purchase Date=nullable date, Store Name=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Purchase Index",1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Customer ID", "Purchase Date", "Store Name", "Purchase Index"}, {"Customer ID.1", "Purchase Date", "Store Name", "Purchase Index"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"Customer ID", "Purchase Date", "Store Name", "Purchase Index"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Purchase Index] = 1))
in
#"Filtered Rows"
This is based on the following blog post if you want to work through the steps yourself.
Create Row Number for Each Group in Power BI using Power Query - RADACAD
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |