Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have data currently shapped as follows:
Row Time Comments
1 1 [Description of work]
2 Null [Continued Description of Work]
3 null [Continued Descrition of Work]
Essentially the only data in row 2 is a continuation of the comments from row 1.
I know in excel could just add a helper column, and have a formula if time > 0, then look at the values in rows 2 and 3 under time for null and append to the commment in row 1.
I haven't been able to figure out how to do this in M/PowerQuery though...
Thanks in advance!
Solved! Go to Solution.
You can fill down the Time column, group on the Time column, chosing a dummy operation for Comments (e.g. max) and then adjust the code to have the text in Comments combined, e.g. with a line feed character like in this query:
let Source = Table1, #"Filled Down" = Table.FillDown(Source,{"Time"}), #"Grouped Rows" = Table.Group(#"Filled Down", {"Time"}, {{"Comments", each Text.Combine([Comments],"#(lf)"), type text}}) in #"Grouped Rows"
You can fill down the Time column, group on the Time column, chosing a dummy operation for Comments (e.g. max) and then adjust the code to have the text in Comments combined, e.g. with a line feed character like in this query:
let Source = Table1, #"Filled Down" = Table.FillDown(Source,{"Time"}), #"Grouped Rows" = Table.Group(#"Filled Down", {"Time"}, {{"Comments", each Text.Combine([Comments],"#(lf)"), type text}}) in #"Grouped Rows"
User | Count |
---|---|
99 | |
86 | |
80 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |