User Profile
SergeiBaklan
Diamond Contributor
Joined 10 years ago
User Widgets
Recent Discussions
Re: Formula help - why doesn't this work for January dates please?
I'm sorry, but formula doesn't ignore texts. ISNUMBER(text) returns zero, but MONTH(text) returns #VALUE!. If multiply zero on (#VALUE!=12) result will be #VALUE!, and SUMPRODUCT with at least one element with error also returns an error.Re: Building relationships in data model to leverage power pivot - circumventing unique key ID issue
I don't remember when TREATAS was introduced in Excel, relatively long ago. However, it's still highlighted by red (as not recognized) in formulae. But it works if to enter it. Usually TREATAS has better performance and easier to use, at least for me.Re: Formula help
Tried to structure your formulae a bit, but without the sample failed to understand what could be wrong =IFERROR( LET( IsD, TRIM('Key Log'!$A$4:$A$309) = TRIM($D$6), IsK, TRIM('Key Log'!$C$4:$C$309) = TRIM($K$15), Include, IsD * IsK, KeyTypes, FILTER('Key Log'!$F$4:$F$309, Include ), KeyNums, FILTER('Key Log'!$K$4:$K$309, Include ), keyNum, INDEX(KeyNums, ROW(A1) ), IF( INDEX(KeyTypes, ROW(A1)) = "Hard Key", "V" & keyNum, keyNum ) ), "") =IF(A8="","", LET( FilteredRows, FILTER( SEQUENCE(ROWS('Key Log'!$A$4:$A$309)), ('Key Log'!$A$4:$A$309=$D$6) * ('Key Log'!$C$4:$C$309=$K$15) ), RowNum, INDEX( FilteredRows, ROW(A1) ), Room, INDEX('Key Log'!$C$4:$C$309, RowNum), Status, INDEX('Key Log'!$E$4:$E$309, RowNum), Type, INDEX('Key Log'!$F$4:$F$309, RowNum), DateVal, INDEX('Key Log'!$D$4:$D$309, RowNum), IsAB, OR( RIGHT(Room)= {"A","B"} ), DoorDesc, IF(IsAB, "Combined Main & Room Door", "Main Door"), KeyDesc, IF(Type="Swipe Key", "Swipe Key", "Hard Key"), DoorDesc & " " ) )Re: How does Excel calculate named formulas and materialized named ranges?
IMHO, your entire named function will be treated as volatile. You may with file provided by Charles Williams within this Volatile Excel Functions -Decision Models post. However, variant 1 and 2 shall be calculated differently. LET() evaluates each internal name, independently will it be used later or not, and on next steps uses result of such evaluation. I guess next steps shall not trigger of OFFSET recalculation within LET for named result. Thus version 1 calculates it once, version two - twice.Re: Getting Data from Multiple Web Pages -- Replicate changes made using Query Editor
The function itself is simple, it just extract information for given ID (pID as text) => let Source = Web.Page(Web.Contents(pURL, [Query = [id=pID]])), Data0 = Source{0}[Data] in Data0 Question is what to do with that. As an example in the query I generated list of ID:s from 1 to 100, picked-up related info with function and expanded everything in one table let Numbers = {1..100}, // ID:s to receive RegNumbers = List.Transform(Numbers, each "R" & Text.Start("000000",6-Text.Length(Text.From(_))) & Text.From(_)), ListToTable = Table.FromList(RegNumbers, Splitter.SplitByNothing(), null, null, ExtraValues.Error), RenameToId = Table.RenameColumns(ListToTable,{{"Column1", "ID"}}), AddRegInfoTables = Table.AddColumn(RenameToId, "Custom", each fnRegistrationParticulars([ID])), ExtractRegNumbers = Table.AddColumn(AddRegInfoTables, "Custom.1", each List.First([Custom][Column2])), FilterEmpty = Table.SelectRows(ExtractRegNumbers, each ([Custom.1] <> "")), ExpandInfo = Table.ExpandTableColumn(FilterEmpty, "Custom", {"Column1", "Column2"}, {"Column1", "Column2"}), KeepInfo = Table.SelectColumns(ExpandInfo,{"Column1", "Column2"}) in KeepInfo Even with hundred ID:s it takes time - each individual request takes few seconds. Other option could be request them one by one if only few are required. Please check attached.Re: PowerPivot not returning correct data
Some cosmetic in addition to Riny_van_Eekelen answer. I'd add actual dates to the data and Date table to the data model it gives more flexibility and reliability working with model. Don't forget to sort period (Date table) in proper order and use it in PivotTable Finally, Microsoft named product as "Power Pivot" but "PivotTable".
