none
Table.Group RRS feed

  • Question

  • Hi: I am trying to perform Table.Group by Location and Type.  After that I want to spread the costs evenly based on the number of days between the relevant start date and end date. I couldn't find any similar examples in the forum and I am not sure if I have to use List.Transform or List.Dates inside the Table.Group to calculate the date range (based on Max and Min). Once this is done I would like pivot the data. Any assistance would be greatly appreciated. Thanks

    Sunday, January 5, 2020 9:52 AM

Answers

  • let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        chtype = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}}),
        group = Table.Group(
                    chtype,
                    "Location",
                    {"t",each [
                                 pvt = Table.Pivot(_, List.Distinct([Type]), "Type", "Amount"),
                                 cmbcol = Table.CombineColumns(pvt,{"Start Date","End Date"},(x)=> List.Generate(()=>x{0},each _ <=x{1},each Date.AddDays(_,1)),"Date"),
                                 lsct = List.Transform(cmbcol[Date],List.Count){0},
                                 trans = Table.TransformColumns(cmbcol,{{"Location",each _},{"Date",each _}},each _/lsct),
                                 expd = Table.ExpandListColumn(trans,"Date")
                              ][expd]
                    }
                ),
        result = #table({"Date"},{}) & Table.Combine(group[t])
    in
        result

    • Marked as answer by M.Awal Thursday, January 9, 2020 10:33 AM
    Wednesday, January 8, 2020 3:46 AM
  • Hi M.Awal

    (Could you think about a thread title that better reflects what you want to achieve?)

    You did not mention how many rows/records are in scope. Last time you were talking about a Million. Not sure if your in the same situation this time (might be important re. pivoting). Below are two options, the 2nd doesn't require pivoting

    Assuming data in Table1

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedTypes = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Type", type text}, {"Amount", type number}, {"Start Date", type date}, {"End Date", type date}}),
        AddedDateList = Table.AddColumn(ChangedTypes, "Date", each List.Dates([Start Date], Duration.Days([End Date]-[Start Date]) +1, #duration(1,0,0,0)), type list),
        AddedDayAmt = Table.AddColumn(AddedDateList, "DailyAmount", each [Amount] / List.Count([Date]), type number),
        SelectColumns = Table.SelectColumns(AddedDayAmt,{"Date", "Location", "Type", "DailyAmount"}),
        ExpandedDate = Table.ExpandListColumn(SelectColumns, "Date"),
        PivotedTypeNoAggreg = Table.Pivot(ExpandedDate, List.Distinct(ExpandedDate[Type]), "Type", "DailyAmount")
    in
        PivotedTypeNoAggreg

    For the 2nd I assumed you knew your Types, hence

    TypeList = {"Salary","Accomodation","Flights","Management Fee"}

    + each Location has each Type. If not the case there's something to do about it

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedTypes = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Type", type text}, {"Amount", type number}, {"Start Date", type date}, {"End Date", type date}}),
        AddedDatesList = Table.AddColumn(ChangedTypes, "Date", each List.Dates([Start Date], (Duration.Days([End Date]-[Start Date])+1), #duration(1,0,0,0)), type list),
        RemovedDates = Table.RemoveColumns(AddedDatesList,{"Start Date", "End Date"}),
        AddedDayAmt = Table.AddColumn(RemovedDates, "DayAmt", each [Amount]/List.Count([Date]), type number),
        RemovedAmount = Table.RemoveColumns(AddedDayAmt,{"Amount"}),
        TypeList = {"Salary","Accomodation","Flights","Management Fee"},
        GroupedRows = Table.Group(RemovedAmount, {"Date","Location"},
            { {"GRP", each Table.SelectColumns(_,{"Type","DayAmt"}), type table} }
        ),
        AddedTypeColumns = List.Accumulate(TypeList, GroupedRows,
            (state,current)=> Table.AddColumn(state, current, each
                [GRP][DayAmt]{List.PositionOf([GRP][Type], current)}, type number)
        ),
        RemovedGroup = Table.RemoveColumns(AddedTypeColumns,{"GRP"}),
        ExpandedDate = Table.ExpandListColumn(RemovedGroup, "Date")
    in
        ExpandedDate

    A variation of the above (same assumptions re. Types):

        TypeList = List.Buffer({"Accomodation","Flights","Management Fee","Salary"}),
        GroupedRows = Table.Group(RemovedAmount, {"Date","Location"},
            { {"GRP", each Table.SelectColumns(_,{"Type","DayAmt"}), type table} }
        ),
        SortedNestedType = Table.TransformColumns(GroupedRows,
            {"GRP", each Table.Sort(_, {"Type",Order.Ascending}) }
        ),
        AddedTypeColumns = List.Accumulate(TypeList, SortedNestedType,
            (state,current)=> Table.AddColumn(state, current, each
                [GRP][DayAmt]{List.PositionOf(TypeList, current)}, type number)
        ),
        RemovedGroup = Table.RemoveColumns(AddedTypeColumns,{"GRP"}),
        ReorderColumns = Table.ReorderColumns(RemovedGroup, {"Date","Location","Salary","Accomodation","Flights","Management Fee"}),
        ExpandedDate = Table.ExpandListColumn(ReorderColumns, "Date")

    • Marked as answer by M.Awal Wednesday, January 8, 2020 10:15 AM
    Sunday, January 5, 2020 3:47 PM
  • Hey M.Awal

    Is this real data or you like to make things complex ;-) ?

    As I suspected earlier that was the reason of the issue (I let you figure it out from there. If you miss something let me know and I'll explain). Option to fix:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedTypes = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Type", type text}, {"Amount", type number}, {"Start Date", type date}, {"End Date", type date}}),
        GroupedRows = Table.Group(ChangedTypes, {"Location"}, {{"GRP", each Table.RemoveColumns(_,{"Location"}), type table}}),
        AddedDatesList = Table.AddColumn(GroupedRows, "DatesList", each
            List.Dates(List.Min([GRP][Start Date]),Duration.Days(List.Max([GRP][End Date]) - List.Min([GRP][Start Date])) +1,#duration(1,0,0,0)), type list
        ),
        AddedDayAmt = Table.TransformColumns(AddedDatesList,
            {"GRP", (nTable)=>
                let
                    dayAmyt = Table.AddColumn(nTable, "DayAmt", each [Amount]/(Duration.Days([End Date]-[Start Date])+1), type number)
                in
                    Table.RemoveColumns(dayAmyt,{"Amount","Start Date","End Date"}), type table
            }
        ),
        AddedTypeColumns = List.Accumulate(List.Distinct(ChangedTypes[Type]), AddedDayAmt,
            (iTable,iType)=> Table.AddColumn(iTable,iType, each
                    try [GRP][DayAmt]{List.PositionOf([GRP][Type],iType)} otherwise null, type number
                )
        ),
        RemovedGroup = Table.RemoveColumns(AddedTypeColumns,{"GRP"}),
        ExpandedDates = Table.ExpandListColumn(RemovedGroup,"DatesList")
    in
        ExpandedDates

    EDIT: To help understanding the issue you got (The index cannot be negative… Index = -1), you'll get the same result as above with the following alternative

        AddedTypeColumns = List.Accumulate(List.Distinct(ChangedTypes[Type]), AddedDayAmt,
            (iTable,iType)=> Table.AddColumn(iTable,iType, each
                //    try [GRP][DayAmt]{List.PositionOf([GRP][Type],iType)} otherwise null, type number
                let
                    pos = List.PositionOf([GRP][Type],iType)
                in
                    if pos = -1 then null else [GRP][DayAmt]{pos}, type number 
    
                )
        ),

    Re. when you use a prev step instead of a scalar value where you populate the seed, how do I know what value it is actually starting from?

    Not sure I understand :-( Could you try in a different way + maybe provide a couple of examples to clarify things? Thanks


    • Edited by Lz._ Tuesday, January 7, 2020 7:29 PM Add. info
    • Proposed as answer by ziying35 Wednesday, January 8, 2020 3:41 AM
    • Marked as answer by M.Awal Thursday, January 9, 2020 10:33 AM
    Tuesday, January 7, 2020 6:47 PM
  • Hi M.Awal

    List. Generate causes a stack overflow error when processing large amounts of data, so this time, my proposal results in another different view in term of dates ranges, I use another method instead of list. Generate, and only used Table.group once

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Change_type = Table.TransformColumnTypes(Source,{{"Start Date", type number}, {"End Date", type number}}),
        Combine_columns = Table.CombineColumns(
                             Change_type,
                             {"Start Date", "End Date"},
                             each [
                                     Date = Expression.Evaluate(Text.Format("{#{0}..#{1}}",_)), 
                                     day_count=List.Count(Date)
                                  ],"Date"
                          ),
        Replace_value_amount = Table.ReplaceValue(
                                  Combine_columns,
                                  each [Amount],
                                  each [Date][day_count],
                                  (x,y,z)=>y/z,
                                  {"Amount"}
                               ),
        Expand_list_column = Table.ExpandRecordColumn(
                                Replace_value_amount,
                                "Date", 
                                {"Date"}
                            ),
        Expand_date = Table.ExpandListColumn(Expand_list_column, "Date"),
        Group_date_location = Table.Group(
                                Expand_date,
                                {"Date","Location"},
                                {"tbl",each Table.Pivot(_, List.Distinct([Type]), "Type", "Amount")}
                              ),
        Combine_tbl = #table({"Date"},{}) & Table.Combine(Group_date_location[tbl]),
        Change_type_date = Table.TransformColumnTypes(Combine_tbl,{{"Date", type date}})
    in
        Change_type_date

    • Marked as answer by M.Awal Thursday, January 9, 2020 10:27 AM
    Thursday, January 9, 2020 12:58 AM
  • I'm sorry, but my English is poor, I reworte the code:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Change_type = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}}),
        Group = Table.Group(
                    Change_type,
                    "Location",
                    {"tbl",each [
                                 pivot = Table.Pivot(_, List.Distinct([Type]), "Type", "Amount"),
                                 combine_col = Table.CombineColumns(
                                                  pivot,
                                                  {"Start Date","End Date"},
                                                  (x)=> [
                                                           date_list = List.Generate(()=>x{0},each _ <=x{1},each Date.AddDays(_,1)),
                                                           day_count = List.Count(date_list)
                                                        ],"Date"
                                                ),
                                 replace_value = Table.ReplaceValue(
                                                    combine_col,
                                                    each [Date][day_count],
                                                    null,
                                                    (x,y,z)=>x/y,
                                                    List.Skip(Table.ColumnNames(combine_col),2)
                                                 ),
                                 expd_rec_col =Table.ExpandRecordColumn(
                                                  replace_value, 
                                                  "Date", 
                                                  {"date_list"}
                                                ),
                                 expd_lst_col = Table.ExpandListColumn(expd_rec_col, "date_list"),
                                 unpivot_oth_col =Table.UnpivotOtherColumns(expd_lst_col, {"date_list", "Location"}, "att", "val"),
                                 group_date = Table.Group(
                                                 unpivot_oth_col,
                                                 "date_list",
                                                 {"tbl_inn",each Table.Pivot(_, List.Distinct([att]), "att", "val")}
                                              )[tbl_inn]
                              ][group_date]
                    }
                ),
        Result = Table.Combine(List.Combine(Group[tbl]))
    in
        Result

    • Marked as answer by M.Awal Thursday, January 9, 2020 10:29 AM
    Wednesday, January 8, 2020 9:51 AM

All replies

  • Hi M.Awal

    (Could you think about a thread title that better reflects what you want to achieve?)

    You did not mention how many rows/records are in scope. Last time you were talking about a Million. Not sure if your in the same situation this time (might be important re. pivoting). Below are two options, the 2nd doesn't require pivoting

    Assuming data in Table1

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedTypes = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Type", type text}, {"Amount", type number}, {"Start Date", type date}, {"End Date", type date}}),
        AddedDateList = Table.AddColumn(ChangedTypes, "Date", each List.Dates([Start Date], Duration.Days([End Date]-[Start Date]) +1, #duration(1,0,0,0)), type list),
        AddedDayAmt = Table.AddColumn(AddedDateList, "DailyAmount", each [Amount] / List.Count([Date]), type number),
        SelectColumns = Table.SelectColumns(AddedDayAmt,{"Date", "Location", "Type", "DailyAmount"}),
        ExpandedDate = Table.ExpandListColumn(SelectColumns, "Date"),
        PivotedTypeNoAggreg = Table.Pivot(ExpandedDate, List.Distinct(ExpandedDate[Type]), "Type", "DailyAmount")
    in
        PivotedTypeNoAggreg

    For the 2nd I assumed you knew your Types, hence

    TypeList = {"Salary","Accomodation","Flights","Management Fee"}

    + each Location has each Type. If not the case there's something to do about it

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedTypes = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Type", type text}, {"Amount", type number}, {"Start Date", type date}, {"End Date", type date}}),
        AddedDatesList = Table.AddColumn(ChangedTypes, "Date", each List.Dates([Start Date], (Duration.Days([End Date]-[Start Date])+1), #duration(1,0,0,0)), type list),
        RemovedDates = Table.RemoveColumns(AddedDatesList,{"Start Date", "End Date"}),
        AddedDayAmt = Table.AddColumn(RemovedDates, "DayAmt", each [Amount]/List.Count([Date]), type number),
        RemovedAmount = Table.RemoveColumns(AddedDayAmt,{"Amount"}),
        TypeList = {"Salary","Accomodation","Flights","Management Fee"},
        GroupedRows = Table.Group(RemovedAmount, {"Date","Location"},
            { {"GRP", each Table.SelectColumns(_,{"Type","DayAmt"}), type table} }
        ),
        AddedTypeColumns = List.Accumulate(TypeList, GroupedRows,
            (state,current)=> Table.AddColumn(state, current, each
                [GRP][DayAmt]{List.PositionOf([GRP][Type], current)}, type number)
        ),
        RemovedGroup = Table.RemoveColumns(AddedTypeColumns,{"GRP"}),
        ExpandedDate = Table.ExpandListColumn(RemovedGroup, "Date")
    in
        ExpandedDate

    A variation of the above (same assumptions re. Types):

        TypeList = List.Buffer({"Accomodation","Flights","Management Fee","Salary"}),
        GroupedRows = Table.Group(RemovedAmount, {"Date","Location"},
            { {"GRP", each Table.SelectColumns(_,{"Type","DayAmt"}), type table} }
        ),
        SortedNestedType = Table.TransformColumns(GroupedRows,
            {"GRP", each Table.Sort(_, {"Type",Order.Ascending}) }
        ),
        AddedTypeColumns = List.Accumulate(TypeList, SortedNestedType,
            (state,current)=> Table.AddColumn(state, current, each
                [GRP][DayAmt]{List.PositionOf(TypeList, current)}, type number)
        ),
        RemovedGroup = Table.RemoveColumns(AddedTypeColumns,{"GRP"}),
        ReorderColumns = Table.ReorderColumns(RemovedGroup, {"Date","Location","Salary","Accomodation","Flights","Management Fee"}),
        ExpandedDate = Table.ExpandListColumn(ReorderColumns, "Date")

    • Marked as answer by M.Awal Wednesday, January 8, 2020 10:15 AM
    Sunday, January 5, 2020 3:47 PM
  • Hi Lz: I thought you would like my title, short & sharp :). I was thinking of solving this problem through Table.Group and apply list.dates inside it. Thanks for providing the solutions. I tried all of the options and they all look elegant. however, I am get some minor errors from option-2 &3 as per below. Looks like you are a big fan of using List.Accumulate. I like it too.

    This may be a dumb question, when you use a prev step instead of a scalar value where you populate the seed, how do I know what value it is actually starting from?

    Monday, January 6, 2020 9:41 AM
  • Sorry forgot to include the error msg:

    Monday, January 6, 2020 9:49 AM
  • Hi MAwal

    Re: I am get some minor errors from option-2 & 3

    Well, not 100% sure but from your last reply where picture shows: The index cannot be negative… Index = -1 I suspect the error comes from the List.PositionOf

    When I suggested options 2 (and it's variation) I mentioned: I assumed you knew your Types... + each Location has each Type

    Could you check this is the reason of the error you get before I suggest a solution to this?

    Re: Looks like you are a big fan of using List.Accumulate

    True but it's not the reason I suggested that possible approach for you case. If you re-read my previous reply you'll see: You did not mention how many rows/records are in scope. Last time you were talking about a Million. Not sure if your in the same situation this time (might be important re. pivoting)

    In other words I tried to find an option to avoid pivoting in case you would have large number of rows and opted for List.Accumulate to add the columns instead of pivoting

     


    • Edited by Lz._ Monday, January 6, 2020 11:44 AM
    Monday, January 6, 2020 11:31 AM
  • Sorry, I am not dealing with huge number of rows like the previous one. Talking 10K rows max. I am happy with the option-1.

    I had look at coding not sure why the error is generating. Both Location and Type field being set as text and still throwing the error. I took off the step declaring data types after source, still doesn't work. With the output for example why $428.57 not showing as circled below?

    https://1drv.ms/x/s!Amc8fiGpDxekg3XymxsgK8smiLWi

    Tuesday, January 7, 2020 9:50 AM
  • Hey M.Awal

    Is this real data or you like to make things complex ;-) ?

    As I suspected earlier that was the reason of the issue (I let you figure it out from there. If you miss something let me know and I'll explain). Option to fix:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedTypes = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Type", type text}, {"Amount", type number}, {"Start Date", type date}, {"End Date", type date}}),
        GroupedRows = Table.Group(ChangedTypes, {"Location"}, {{"GRP", each Table.RemoveColumns(_,{"Location"}), type table}}),
        AddedDatesList = Table.AddColumn(GroupedRows, "DatesList", each
            List.Dates(List.Min([GRP][Start Date]),Duration.Days(List.Max([GRP][End Date]) - List.Min([GRP][Start Date])) +1,#duration(1,0,0,0)), type list
        ),
        AddedDayAmt = Table.TransformColumns(AddedDatesList,
            {"GRP", (nTable)=>
                let
                    dayAmyt = Table.AddColumn(nTable, "DayAmt", each [Amount]/(Duration.Days([End Date]-[Start Date])+1), type number)
                in
                    Table.RemoveColumns(dayAmyt,{"Amount","Start Date","End Date"}), type table
            }
        ),
        AddedTypeColumns = List.Accumulate(List.Distinct(ChangedTypes[Type]), AddedDayAmt,
            (iTable,iType)=> Table.AddColumn(iTable,iType, each
                    try [GRP][DayAmt]{List.PositionOf([GRP][Type],iType)} otherwise null, type number
                )
        ),
        RemovedGroup = Table.RemoveColumns(AddedTypeColumns,{"GRP"}),
        ExpandedDates = Table.ExpandListColumn(RemovedGroup,"DatesList")
    in
        ExpandedDates

    EDIT: To help understanding the issue you got (The index cannot be negative… Index = -1), you'll get the same result as above with the following alternative

        AddedTypeColumns = List.Accumulate(List.Distinct(ChangedTypes[Type]), AddedDayAmt,
            (iTable,iType)=> Table.AddColumn(iTable,iType, each
                //    try [GRP][DayAmt]{List.PositionOf([GRP][Type],iType)} otherwise null, type number
                let
                    pos = List.PositionOf([GRP][Type],iType)
                in
                    if pos = -1 then null else [GRP][DayAmt]{pos}, type number 
    
                )
        ),

    Re. when you use a prev step instead of a scalar value where you populate the seed, how do I know what value it is actually starting from?

    Not sure I understand :-( Could you try in a different way + maybe provide a couple of examples to clarify things? Thanks


    • Edited by Lz._ Tuesday, January 7, 2020 7:29 PM Add. info
    • Proposed as answer by ziying35 Wednesday, January 8, 2020 3:41 AM
    • Marked as answer by M.Awal Thursday, January 9, 2020 10:33 AM
    Tuesday, January 7, 2020 6:47 PM
  • let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        chtype = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}}),
        group = Table.Group(
                    chtype,
                    "Location",
                    {"t",each [
                                 pvt = Table.Pivot(_, List.Distinct([Type]), "Type", "Amount"),
                                 cmbcol = Table.CombineColumns(pvt,{"Start Date","End Date"},(x)=> List.Generate(()=>x{0},each _ <=x{1},each Date.AddDays(_,1)),"Date"),
                                 lsct = List.Transform(cmbcol[Date],List.Count){0},
                                 trans = Table.TransformColumns(cmbcol,{{"Location",each _},{"Date",each _}},each _/lsct),
                                 expd = Table.ExpandListColumn(trans,"Date")
                              ][expd]
                    }
                ),
        result = #table({"Date"},{}) & Table.Combine(group[t])
    in
        result

    • Marked as answer by M.Awal Thursday, January 9, 2020 10:33 AM
    Wednesday, January 8, 2020 3:46 AM
  • (@Awal: ziying's proposal results in a different view in term of dates ranges. You might be interested…)

    Hi ziying

    trans = Table.TransformColumns(cmbcol,{{"Location",each _},{"Date",each _}},each _/lsct)

     Will UpVote your reply in a minute for the Table.TransformColumn part I highlighted above (a take away for me). No problem to follow/understand your other steps. However, I do have a problem the following (not to understand it):

    lsct = List.Transform(cmbcol[Date],List.Count){0}

    Let's take [Location]=ABC only and Type={"Salary","Flights"} only, just to clarify my point...

    In this scenario you output a Dates range from 01/07/19-10/07/19 (so 10 dates) for Salary and from 04/07/19-10/07/19 for Flights (so 7 dates). All is fine so far and that output might be interesting (hence my comment @Awal). However, by taking the 1st item ({0}) you not only divide [Salary] by 10, but [Flights] as well. Shouldn't [Flights] be divided by 7 only?

    Other than that I might have another question that has nothing to do with the above one but re. your step

    cmbcol = Table.CombineColumns(…)

    I'll do my home work re. the question I have in mind. If I can't answer myself may I raise it to you? Thanks

    Wednesday, January 8, 2020 8:06 AM
  • I'm sorry, but my English is poor, I reworte the code:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Change_type = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}}),
        Group = Table.Group(
                    Change_type,
                    "Location",
                    {"tbl",each [
                                 pivot = Table.Pivot(_, List.Distinct([Type]), "Type", "Amount"),
                                 combine_col = Table.CombineColumns(
                                                  pivot,
                                                  {"Start Date","End Date"},
                                                  (x)=> [
                                                           date_list = List.Generate(()=>x{0},each _ <=x{1},each Date.AddDays(_,1)),
                                                           day_count = List.Count(date_list)
                                                        ],"Date"
                                                ),
                                 replace_value = Table.ReplaceValue(
                                                    combine_col,
                                                    each [Date][day_count],
                                                    null,
                                                    (x,y,z)=>x/y,
                                                    List.Skip(Table.ColumnNames(combine_col),2)
                                                 ),
                                 expd_rec_col =Table.ExpandRecordColumn(
                                                  replace_value, 
                                                  "Date", 
                                                  {"date_list"}
                                                ),
                                 expd_lst_col = Table.ExpandListColumn(expd_rec_col, "date_list"),
                                 unpivot_oth_col =Table.UnpivotOtherColumns(expd_lst_col, {"date_list", "Location"}, "att", "val"),
                                 group_date = Table.Group(
                                                 unpivot_oth_col,
                                                 "date_list",
                                                 {"tbl_inn",each Table.Pivot(_, List.Distinct([att]), "att", "val")}
                                              )[tbl_inn]
                              ][group_date]
                    }
                ),
        Result = Table.Combine(List.Combine(Group[tbl]))
    in
        Result

    • Marked as answer by M.Awal Thursday, January 9, 2020 10:29 AM
    Wednesday, January 8, 2020 9:51 AM
  • @Awal

    If my last proposal doesn't work as you would expect then confirm my understanding what you expect is:

    • IF ([End Date] - [Start Date]) = 10days for [Type]= Salary THEN calc. [Salary]/10
    • IF ([End Date] - [Start Date]) = 5days for [Type]=Flights THEN calc. [Flights]/5
    • ...

    and I'll rework it

    @ziying

    No worries re. English, I'm not native either. And thanks for revising your proposal, I'll have a look at it on my spare time as this looks advanced "coding" and there are probably something to take away from this :-)

    Wednesday, January 8, 2020 12:04 PM
  • Hi M.Awal

    List. Generate causes a stack overflow error when processing large amounts of data, so this time, my proposal results in another different view in term of dates ranges, I use another method instead of list. Generate, and only used Table.group once

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Change_type = Table.TransformColumnTypes(Source,{{"Start Date", type number}, {"End Date", type number}}),
        Combine_columns = Table.CombineColumns(
                             Change_type,
                             {"Start Date", "End Date"},
                             each [
                                     Date = Expression.Evaluate(Text.Format("{#{0}..#{1}}",_)), 
                                     day_count=List.Count(Date)
                                  ],"Date"
                          ),
        Replace_value_amount = Table.ReplaceValue(
                                  Combine_columns,
                                  each [Amount],
                                  each [Date][day_count],
                                  (x,y,z)=>y/z,
                                  {"Amount"}
                               ),
        Expand_list_column = Table.ExpandRecordColumn(
                                Replace_value_amount,
                                "Date", 
                                {"Date"}
                            ),
        Expand_date = Table.ExpandListColumn(Expand_list_column, "Date"),
        Group_date_location = Table.Group(
                                Expand_date,
                                {"Date","Location"},
                                {"tbl",each Table.Pivot(_, List.Distinct([Type]), "Type", "Amount")}
                              ),
        Combine_tbl = #table({"Date"},{}) & Table.Combine(Group_date_location[tbl]),
        Change_type_date = Table.TransformColumnTypes(Combine_tbl,{{"Date", type date}})
    in
        Change_type_date

    • Marked as answer by M.Awal Thursday, January 9, 2020 10:27 AM
    Thursday, January 9, 2020 12:58 AM
  • Hi Ziying: Thanks for your time and sharing your solutions. Very good and smart coding you have demonstrated. You must be play around with M a lot. I have another date related problem which I posted previously didn't get a proper solution. I will create a new thread on that if you get a chance to have a look, that will be great.

    @Lz_

    "IF ([End Date] - [Start Date]) = 10days for [Type]= Salary THEN calc. [Salary]/10" I can confirm what you stated is correct. Thanks

     

    Thursday, January 9, 2020 10:27 AM
  • @LZ: Pls ignore my previous reply. Your solution does provide correct result. Thx.
    Thursday, January 9, 2020 10:33 AM
  • @M.Awal:

    I like working with data in M, and I'd like to see the problem that you said, Can you show me the link?

    Thursday, January 9, 2020 2:48 PM
  • Hi Ziying: Following is the thread title, if you do a search you will find it. Please refer to my last two posts it has got the screen shot and file what I want to achieve. Thanks

    Table.SelectRows based on the input from a separate query

     

    Friday, January 10, 2020 10:42 AM