Forum Discussion
Execute user function per row or group
Hi, I have a table with dates and I would like to add a new date row for each month since the first create date until today for each issueid. I have created a user function which will genereate ...
As I can see from your M code that you are only filtering the records to get the values of that particular record.
What user function are you using?? SYNTEX and other details. Posting a Sample data file can help me to demostrate you the different ways.
It is really confusing to follow the M codes with limited data available.
Hi again,
I'm filtering on the [RESOLUTION] not the [issueid].
Here is a sample of the data.
ID issueid AUTHOR CREATED RESOLUTION 2693573 225389 shirasawa 01-12-2015 06:28:28 null 2693613 225389 gaya 01-12-2015 07:02:37 null 2693614 225389 gaya 01-12-2015 07:03:29 null 2693615 225389 gaya 01-12-2015 07:03:33 null 2693616 225389 gaya 01-12-2015 07:03:37 null 2693617 225389 gaya 01-12-2015 07:03:38 null 2693618 225389 gaya 01-12-2015 07:03:48 null 2693619 225389 gaya 01-12-2015 07:03:56 null 2694867 225389 mktj 01-12-2015 14:17:44 null 2694884 225389 rasr 01-12-2015 14:23:03 null 2702181 225389 dinc 03-12-2015 16:13:42 null 2702378 225389 dinc 03-12-2015 17:02:29 null 2702381 225389 dinc 03-12-2015 17:02:38 null 2702382 225389 dinc 03-12-2015 17:02:38 null 2704234 225389 shirasawa 04-12-2015 10:38:35 null 2704235 225389 shirasawa 04-12-2015 10:38:35 null 2710810 225389 dinc 07-12-2015 16:11:05 null 2714257 225389 dinc 08-12-2015 17:11:57 null 2714258 225389 dinc 08-12-2015 17:12:06 null 2714259 225389 dinc 08-12-2015 17:12:06 null 2724177 225389 shirasawa 14-12-2015 04:08:58 null 2724178 225389 shirasawa 14-12-2015 04:08:58 null 2725238 225389 dinc 14-12-2015 12:42:30 null 2727985 225389 dinc 15-12-2015 10:41:18 null 2728029 225389 dinc 15-12-2015 10:50:12 null 2728057 225389 dinc 15-12-2015 10:56:58 null 2728063 225389 dinc 15-12-2015 10:57:28 null 2728069 225389 dinc 15-12-2015 10:58:04 null 2728370 225389 rasr 15-12-2015 11:59:02 null 2728982 225389 alni 15-12-2015 14:16:02 null 2730066 225389 misk 15-12-2015 17:47:10 null 2730067 225389 misk 15-12-2015 17:47:20 null 2730155 225389 alni 15-12-2015 18:19:15 null 2743837 225389 dinc 22-12-2015 15:00:10 null 2749375 225389 lpgr 29-12-2015 13:14:46 null 2749505 225389 ivpr 29-12-2015 14:05:02 null 2749508 225389 ivpr 29-12-2015 14:05:31 null 2749510 225389 ivpr 29-12-2015 14:05:41 null 2749511 225389 ivpr 29-12-2015 14:05:49 null 2822774 225389 trha 19-01-2016 13:26:45 null 2907333 225389 misk 04-02-2016 22:09:07 null 2907508 225389 misk 04-02-2016 22:29:42 null 3008659 225389 misk 26-02-2016 11:06:14 null 3119544 225389 misk 19-04-2016 23:16:59 null 3371809 225389 misk 25-08-2016 10:03:53 null 3371810 225389 misk 25-08-2016 10:04:19 null 3371811 225389 misk 25-08-2016 10:04:20 null 3371822 225389 hbsn 25-08-2016 10:06:22 null 3373261 225389 dinc 25-08-2016 17:01:16 null 3374753 225389 misk 26-08-2016 11:00:33 null 3376574 225389 dinc 27-08-2016 08:22:15 null 2693678 225409 murv 01-12-2015 08:09:30 null 2693751 225409 nade 01-12-2015 08:41:17 null 2693972 225409 nade 01-12-2015 10:09:14 null 2694637 225409 murv 01-12-2015 13:34:12 null 2694639 225409 murv 01-12-2015 13:34:29 null 2694641 225409 murv 01-12-2015 13:34:43 null 2696518 225588 d.tonak 02-12-2015 08:56:13 null 2696587 225588 misk 02-12-2015 09:15:50 null 2696590 225588 misk 02-12-2015 09:17:47 null 2696591 225588 misk 02-12-2015 09:17:53 null 2696592 225588 misk 02-12-2015 09:17:56 null 2696593 225588 misk 02-12-2015 09:18:30 null 2696594 225588 misk 02-12-2015 09:18:34 null 2696595 225588 misk 02-12-2015 09:18:34 null 2696596 225588 misk 02-12-2015 09:18:51 null 2697860 225588 escl 02-12-2015 13:49:17 null 2697865 225588 escl 02-12-2015 13:51:18 null 2697866 225588 escl 02-12-2015 13:53:33 null 2697868 225588 escl 02-12-2015 13:53:54 null 3013799 225588 petb 29-02-2016 17:43:58 null 3013942 225588 petb 29-02-2016 18:55:07 null 3013943 225588 petb 29-02-2016 18:55:23 null 3013944 225588 petb 29-02-2016 18:55:40 null
Table Jira changegroup m-code:
let
Source = MySQL.Database("jira7-db.xx.corp", "jira", [Query="select changegroup.* from changegroup, jiraissue where issueid = jiraissue.id and jiraissue.created > ADDDATE(LAST_DAY(SUBDATE(curdate(), INTERVAL 1 YEAR)), 1)", ReturnSingleDatabase=true]),
#"Merged Queries" = Table.NestedJoin(Source,{"issueid"},#"jira jiraissue",{"ID"},"NewColumn",JoinKind.Inner),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"RESOLUTION"}, {"NewColumn.RESOLUTION"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded NewColumn",{{"NewColumn.RESOLUTION", "RESOLUTION"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([RESOLUTION] = null)),
#"Find Dates" = FindDates(#"Filtered Rows"[CREATED]{0}, #"Filtered Rows"[issueid]{0})
in
#"Find Dates"
User function FindDates:
(date as datetime, issue as number) =>
let
month=Date.Month(date),
year=Date.Year(date),
MonthList=if year < Date.Year(DateTime.Date(DateTime.LocalNow()))
then
List.Combine({List.Transform(List.Numbers(month,13-month),
each Text.From(Date.Year(DateTime.Date(DateTime.LocalNow()))-1)&"-"&Text.From(_)&"-1"), List.Transform(List.Numbers(1,Date.Month(DateTime.Date(DateTime.LocalNow()))),
each Text.From(Date.Year(DateTime.Date(DateTime.LocalNow())))&"-"&Text.From(_)&"-1")})
else
List.Transform(List.Numbers(month,Date.Month(DateTime.Date(DateTime.LocalNow()))-month+1),
each Text.From(Date.Year(DateTime.Date(DateTime.LocalNow())))&"-"&Text.From(_)&"-1"),
DateList=List.Transform(MonthList, each DateTime.FromText(_)),
DaysList=List.Transform(DateList, each Date.DaysInMonth(_)),
TableList=Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
AddedIssueId = Table.AddColumn(TableList, "[issueid]", each issue),
AddedId = Table.AddColumn(AddedIssueId, "[ID]", each 0),
AddedAuthor = Table.AddColumn(AddedId, "[AUTHOR]", each "Power BI"),
Renamed = Table.RenameColumns(AddedAuthor,{{"Column1", "CREATED"}}),
ChangedType = Table.TransformColumnTypes(Renamed,{{"CREATED", type datetime}})
in
ChangedType