Forum Discussion

eacy's avatar
eacy
Helper II
9 years ago

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 ...
Need Help
BhaveshPatel's avatar
BhaveshPatel
Super User
9 years ago

What do you want to achieve for different user IDs. What is the your expected output. There are many ways to do this in PowerBI.

eacy's avatar
eacy
Helper II
9 years ago

Hi Bhavesh,

 

I want a table like this one.

 

this one contains one row for each month since the creation date for this specific issue id (225389).

 

My problem is the user function is only executed one time for the first issue id in the original table. I would like to executed it one time for each of the issues in the original table.

The business case is that i want to ensure that I have at least one entry per month per issue id. I have arount 1000 different issue ids

  • BhaveshPatel's avatar
    BhaveshPatel
    Super User
    9 years ago

    eacy

    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.

    • eacy's avatar
      eacy
      Helper II
      9 years ago

      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