Expand a numeric range with each value

Hi  cosminc

Create a new calculated table:

NewTable =
GENERATE (
    SUMMARIZECOLUMNS ( Table1[Salesman]; Table1[City] ),
    GENERATESERIES (
        CALCULATE ( DISTINCT ( Table1[from] ) ),
        CALCULATE ( DISTINCT ( Table1[to] ) )
    )
)

The columns 'from' and 'to' need to be numbers, not text. You can then change the name of the column 'Value' to 'YearMonth'. This could also be done in the code through SELECTCOLUMNS but it's probably not worth it.

EDIT: gooranga1is right. It won't work across years. You can fix it like this (admittedly not the most efficient way):

NewTable_v2 =
GENERATE (
    SUMMARIZECOLUMNS ( Table1[Salesman]; Table1[City] );
    FILTER (
        GENERATESERIES (
            CALCULATE ( DISTINCT ( Table1[from] ) );
            CALCULATE ( DISTINCT ( Table1[to] ) )
        );
        MOD ( [Value]; 100 ) IN GENERATESERIES ( 1; 12 )
    )
)

Hi,

Try this M code

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Salesman", type text}, {"From", Int64.Type}, {"To", Int64.Type}, {"City", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Number.ToText([From])&"01"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Number.ToText([To])&"01"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"From", "To"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns", "From", each Date.From([Custom], "en-IN")),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Custom"}),
    #"Added Custom3" = Table.AddColumn(#"Removed Columns1", "To", each Date.From([Custom.1], "en-IN")),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom3",{"Custom.1"}),
    #"Added Custom4" = Table.AddColumn(#"Removed Columns2", "Custom", each {Number.From([From])..Number.From([To])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom4", "Custom"),
    #"Removed Columns3" = Table.RemoveColumns(#"Expanded Custom",{"From", "To"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns3",{{"Custom", "Date"}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type date}}, "en-IN"),
    #"Calculated Start of Month" = Table.TransformColumns(#"Changed Type with Locale",{{"Date", Date.StartOfMonth, type date}}),
    #"Removed Duplicates" = Table.Distinct(#"Calculated Start of Month"),
    #"Added Custom5" = Table.AddColumn(#"Removed Duplicates", "Custom", each Number.ToText(Date.Year([Date]))&Text.PadStart(Number.ToText(Date.Month([Date])),2,"0")),
    #"Removed Columns4" = Table.RemoveColumns(#"Added Custom5",{"Date"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns4",{"Salesman", "Custom", "City"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "YearMonth"}})
in
    #"Renamed Columns1"

Hope this helps.

cosminc 

My notifications are not working so I hadn't seen your question below.

This is used to extract the last two digits of the number (so it's the month here). MOD gives you the reminder when dividing by a number, 100 in this case.  

---

cosminc wrote:

i used la last syntax in 2 situations,

what does this part 

MOD ( [Value]; 100 )