Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello
Imagine the following table in PowerBI view. This data comes from 2 related tables (employee and matchprofiles, 1:N)
Employee MatchProfileID Shortdescription - Othertext Startdate
Employee A 1 Hello Bonjour 2016-01-01
Employee A 2 Something Something2 2016-02-01
Employee A 3 Yess.. No 2016-12-10
Employee A 4 Brr. Brr22 2016-12-30
I’m looking for a merge function for shortdescription & Othertext of multiple rows with a startdate the upcoming 30 days. The result in a table should be something like this is a PowerBI table view:
Hello,
Imagine the following table in PowerBI view. This data comes from 2 related tables (employee and matchprofiles, 1:N)
Employee MatchProfileID Shortdescription - Othertext Startdate
Employee A 1 Hello Bonjour 2016-01-01
Employee A 2 Something Something2 2016-02-01
Employee A 3 Yess.. No 2016-12-10
Employee A 4 Brr. Brr22 2016-12-30
Employee is related to the other table (1:N)
I’m looking for a merge function for shortdescription & Othertext (multiple rows) with a startdate the upcoming 30 days. The result in a table should be something like this is a PowerBI table view:
Employee Mergedescription&Othertext
Employee A Yess&No, Brr.Brr22 -> merges in one field.
I tried something with “PATH”, but got an error about not containing values because the fields I want to show not are the table primary en secondary keys.
Employee A Yess&No, Brr.Brr22 -> merges in one field.
I tried something with “PATCH”, but got an error about not containing values because the fields I want to show not are the table primary en secondary keys.
First: Is this possible in a medium sized dataset? Any ideas to achieve this?
>> I’m looking for a merge function for shortdescription & Othertext of multiple rows with a startdate the upcoming 30 days.
What do you mean the upcoming 30 days? For example, the upcoming 30 days of 2016-12-10 is 2017-01-09, you will merge shortdescription & Othertext for the date less than 2017-01-09 to one row where startdate 2016-12-10 shown in pivot table as follows. But if the row where the date is less than upcoming 30 days (eg,2016-12-30) will be include for another group(from 2016-12-30 to 2017-12-29 )? Could you share more details or post an overall example? And do you want to merge function for shortdescription & Othertext (multiple rows) with a startdate the upcoming 30 days by creating calculated column or measure?
For merging shortdescription & Othertext group by employee, you can use the CONCATENATEX Function to merge multiple row to one. The instance in this link describes the function clearly.
In addition, the Path is unsuitable for getting what you want. Path function displays Parent-Child Hierarchies in DAX, and returns a delimited text with the identifiers of all the parents to the current row, starting with the oldest or top most until current. This article help you better understanding the PATH function.
Best Regards,
Angelia
Hello,
Imagine the following table in PowerBI view. The underlying data comes from 2 related tables (employee and matchprofiles, 1:N)
Employee | MatchProfileID | Shortdescription | Othertext | Startdate |
Employee A | 1 | Hello | Bonjour |
2016-01-01 |
Employee A | 2 | Something | Something2 |
2016-01-01 |
Employee A | 3 | Yess.. | No |
2016-10-10 |
Employee A | 4 | Brr. | Brr22 | 2016-12-10 |
I’m looking for a merge function for shortdescription & Othertext (multiple rows) with a startdate the upcoming 30 days.
The result in a PowerBI table view should be something like this:
Employee | Mergedescription&Othertext |
Employee A | Yess&No, Brr.&Brr22 |
-> one row!
I tried something with “PATH”, but got an error about not containing values because the fields I want to show aren't the table primary en secondary keys. The dataset is medium sized.
Some ideas ar welcome! Thank you!
First you have to filter your table (I used a parameter here, but ou could also use a formula if it should always be the last 30 days before today for example):
Table.SelectRows(#"Changed Type", each [Startdate] > DateParameter)
Then you add a column called "Description" where you concatenate the values of the 2 columns like this:
= Table.AddColumn(#"Filtered Rows", "Description", each [Shortdescription]&"&"&[Othertext])
Then you group on Employee ID and combine all rows of one Employee ID like this:
Table.Group(#"Added Custom", {"Employee"}, {{"All", each Text.Combine(_[Description], ", "), type table}})
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |