Forum Discussion
Conditional Path function
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}})
