Forum Discussion
Helper IThere's not enough memory to complete this operation. when creating a measure
Community Champion
ok i just changed the data types of your fields because using text was making it ugly and values and concatenate = performance hungry.
i am still not sure if this is what you want it to do but this is what ive done and it ran succesfully (took a minute or 2 though), still thinking creating a code in power query might be better.
date2 = FORMAT(CMS[Date],"General Number")
Code2 = CONCATENATE(CMS[date2],CMS[Login ID])
Seq = COUNTAX(FILTER(CMS,[Avail Time]<=EARLIER([Avail Time])&&[Code2]=EARLIER([Code2])),[Code2])
Helper I
sorry it still loading more then give me the mentioned error, I need to clarify what the result that I need to get, regardless the used equation, you could try an easier one.
we will override the query that I applied, I've 2 columns [Code] has duplicated records, [Avail Time] has multiple records for such code, I need [Max Avail] column, I need to get the maximum avail record for every [Code] value then return the other to be 0 like listed in pic
thanks a lot,
- ImkeF
If you use the query-editor instead, you will not experience these performance problems here, because you can "partition" your tabel on the code and operate on much smaller chunks there:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Login ID", Int64.Type}, {"Avail Time", Int64.Type}, {"Code", Int64.Type}}), GroupCode = Table.Group(#"Changed Type", {"Code"}, {{"Partition", each _, type table}}), Function = (Table) => let Seq = Table.AddColumn(Table, "Seq", each Table.RowCount(Table.SelectRows(Table, (this)=>this[Avail Time]<=[Avail Time]))), #"Added Custom1" = Table.AddColumn(Seq, "Max Avail", each List.Max(Seq[Seq])), #"Added Custom" = Table.AddColumn(#"Added Custom1", "Last Avail", each if [Seq]=[Max Avail] then [Avail Time] else null) in #"Added Custom", #"Added Custom" = Table.AddColumn(GroupCode, "Custom", each Function([Partition])), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Date", "Login ID", "Avail Time", "Seq", "Max Avail", "Last Avail"}, {"Date", "Login ID", "Avail Time", "Seq", "Max Avail", "Last Avail"}) in #"Expanded Custom"or if you just need the "Last Avail":
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Login ID", Int64.Type}, {"Avail Time", Int64.Type}, {"Code", Int64.Type}}), GroupCode = Table.Group(#"Changed Type", {"Code"}, {{"MaxAvail", each List.Max([Avail Time]), type number}, {"OtherColumns", each _, type table}}), #"Expanded OtherColumns" = Table.ExpandTableColumn(GroupCode, "OtherColumns", {"Date", "Login ID", "Avail Time"}, {"Date", "Login ID", "Avail Time"}), #"Added Custom1" = Table.AddColumn(#"Expanded OtherColumns", "Max Avail", each if [MaxAvail]=[Avail Time] then [MaxAvail] else null) in #"Added Custom1"How to integrate M-code into your solution -- Check out more PBI- learning resources here
- vanessafvg
AAbdelkader so this might be a dumb question why dont you group by thee relevant fields ie code date etc and then get the max time.
so in other words in power query, create the composite key that makes the code unique by creating a custom column and then group by that key and bring max the max available time, or am i misunderstanding your requirement?
