Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have two tables with same structures (tickets), one contains the all data (unchanging) and the other (new data). Based on the day, the new data can contain rows from the all data. How can I merge those two tables in Power Query M ?
All the tickets
Key | Description | Date Worked On |
1 | Fix my problem | 01/01/2021 |
2 | I want a laptop | 02/02/2021 |
Tickets that got reopened
Key | Description | Date Worked On |
2 | I want a laptop | 03/02/2021 |
3 | This is a new | 03/03/2021 |
All the tickets data should look like this
Key | Description | Date Worked On |
1 | Fix my problem | 01/01/2021 |
2 | I want a laptop | 03/02/2021 |
3 | This is a new | 03/03/2021 |
Solved! Go to Solution.
@workmonitored as a rule of thumb and best practice, do transformation/data preparation as upstream as possible, and as close to the source.
if you can do this in an SQL server then better to do it there so that it can be used for other reports/projects as well instead of doing it in Power BI.
Hope this helps.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@workmonitored anhow you have both the solution, should be easy to test without too much efforts, not sure what else to tell. Thanks!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thank you. Is there a recommended programming language for integrating SQL Server with a rest api endpoint ?
Hi @workmonitored ,
You can integrate SQL Server with a Rest API endpoint through C#.
Please check the links below.
Calling REST API Service From SQL Server Using C# SQL CLR
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@workmonitored well there is always a decision to make, you can test both approaches (since you have both solutions) and go from there. Not sure why there is schedule dependency on SQL, you will create a view which will prepare the data, and then Power BI will connect to that view, in this case, there is no schedule on the SQL side, or maybe I'm not fully sure about your setup.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks. The data comes from a REST api resource. It has two call signatures. One that are active and one that's not. That's why I have to refresh the active data once a day or so. Hence the schedule....
@workmonitored at this point performance is more of SQL server, not Power BI. you have to make sure your queries in the SQL server are optimized, Power BI is just reading data and will depend on the SQL server.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@workmonitored as a rule of thumb and best practice, do transformation/data preparation as upstream as possible, and as close to the source.
if you can do this in an SQL server then better to do it there so that it can be used for other reports/projects as well instead of doing it in Power BI.
Hope this helps.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thank you. Will move it to SQL. My only gripe is the refresh schedule of Power BI and SQL refresh will have to be in sync. I hate creating dependencies with two different systems.
Hi,
This M code works
let
Source = Table.Combine({All, reopened}),
#"Grouped Rows" = Table.Group(Source, {"Key"}, {{"Count", each Table.Max(_,"Date Worked On")}}),
#"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"Description", "Date Worked On"}, {"Description", "Date Worked On"})
in
#"Expanded Count"
Hope this helps.
Thank you. This is very smart. Side question, should I worry about refresh times, the all table contains rows upwards of 100K and the new table has about 1-2K rows which gets refreshed daily.
hi @workmonitored ,
try like:
append them, sort by date column, select key column and remove duplicate rows
or
left outer join the reopenned table to all ticket table based on key column; expand the date column from reopenned , remove the original date column
@workmonitored if that it the case, append both the table and group it get the latest date, here is M code, start a blank query, click advanced editor and paste the code below, change the name of the table in the first line:
let
Source = Table.Combine({All, Open}),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Grouped Rows" = Table.Group(#"Added Index", {"Key", "Description"}, {{"Latest Date", each List.Max([Date Worked On]), type nullable date}})
in
#"Grouped Rows"
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thank you. Should I be worried about doing this in PowerBI and not a sql server. Not sure how performant PowerBI will be. I am guessing that's why there is an index column in there. I am talking about hundred thousand rows and updating 2K of them.
@workmonitored where the records are matching you want to keep the newest one, in this case # 2?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
yes
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
88 | |
83 | |
64 | |
49 |
User | Count |
---|---|
127 | |
108 | |
87 | |
70 | |
66 |