Forum Discussion
Post PartisanExpand 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.
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 )
Community ChampionHi 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 )
)
)
gooranga1Power Participant
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
.
cosmincthanks both of you that helped me, i'll try them asap
- 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
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