Expand a numeric range with each value

[cosminc]

Hi

is it posibile to transform a table like this

Salesman      from                  to                City

George          201701           201703          New York

Michael          201801          201804          London

George           201801          201802          Paris

in a table like this:

Salesman      YearMonth     City

George          201701         New York

George          201702         New York

George          201703         New York

Michael          201801         London

Michael          201802         London

Michael          201803         London

Michael          201804         London

George          201801        Paris

George          201802        Paris

Thanks in advance,

Cosmin

[AlB · Accepted solution]

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 )
    )
)

[Ashish_Mathur · Accepted solution]

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.

[Ashish_Mathur · Accepted solution]

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]

thanks,

it work fine

very helpfull all the solutions!

Cosmin

[AlB · Accepted solution]

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 )
    )
)

[cosminc]

Hi,

thanks again

i used la last syntax in 2 situations,

what does this part 

MOD ( [Value]; 100 )

? 

thanks for your help

Cosmin

[AlB · Accepted solution]

@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 )

[cosminc]

Hi

Thanks

your solution is good also

i'm doing some tests regarding refresh time and resources usages

2 situations:

1. your solution in DAX conected with other base and used relative to add a column needed from tha base created

2 Ashish's solution put in Query editor and merged in the main base where i added the column needed from the base created by Ashish (for main source a use a folder source and put merge function in a transform file - transform sample file; i've read that is better to manipulate the base there than the base itself )

results puzzled me

solution 1 obtain a reload with 10 min better but the pbix of Ashish reloads harder but is smaller with 5Mega

any advice?

i preffered to merge the basis on power editor and not to load Ashish table and make a relationship between the 2 basis

is it wrong or better?

Thanks,

Cosmin

[gooranga1]

I think the to and from would need to be converted to an actual date to create the series as and time spans between years wouldn't work.

I added a new row spanning a year.

From Date = DATE(left(Table18[from],4),right(Table18[from],2),1)

To Date = date(left(Table18[to],4),right(Table18[to],2),1)

Then the generateseries can be created from there.

NewTable1 = 
GENERATE (
    SUMMARIZECOLUMNS ( Table18[Salesman], Table18[City] ),
    GENERATESERIES (
        CALCULATE ( VALUES( Table18[From Date] ) ),
        CALCULATE ( VALUES(  Table18[To Date] ) )
    )
)

To stop all dates showing create a new column in the new table to extract the day of the month and filter on 1. Possibly you can do this in the generate statement but I don't know how.

FirstDayofMonth = day(NewTable1[Value])

So they look like this

.

[cosminc]

thanks both of you that helped me, i'll try them asap