User Profile
Bridgett_B
Copper Contributor
Joined 4 years ago
User Widgets
Recent Discussions
Index Match Match not returning expected results, referencing structured Table columns
Hi Everyone, I've been looking at this so long with no success and I think I just need a fresh set of eyes/another opinion so I would appreciate any input you may have. I'm using Office 365. I've used Power Query to give me the table below, which is named Revenue___Expense_GRNP_Trial: I'm using an index match match formula to pull that data into a separate tab of the same workbook with this formula =(IFERROR(INDEX(Revenue___Expense_GRNP_Trial[[Amount]:[Amount]],MATCH('Program Revenues-Schools'!$B10,Revenue___Expense_GRNP_Trial[[Account Description]:[Account Description]],0),MATCH('Program Revenues-Schools'!D$5,Revenue___Expense_GRNP_Trial[[Fund No.]:[Fund No.]],0)),0)) That formula works for only the first "fund" in that table even though my formula is referencing that entire fund column. I say this because it won't work for any other fund in the list and if I change the sort on that original table so that the fund is descending instead of ascending, it won't even pick up fund 001-0000. Everything is formatted the same, "general" and if you look at column I, fund 006-0000 is definitely in the original data table. It's like the reference range is only looking at a limited number of rows in the table and I can't see where the error in my INDEX formula is. Any help would be appreciated! Bridgettpower query with relative data source not refreshing accurate data
I have a query that is referencing a relative data source that works fine when that source is a specific file. However when I try to direct it to a specific table within that file it isn't reading the data correctly. I'm using Excel 2016. There are only 2 tabs in my Test.xlsx workbook, a settings tab that holds the file location and a tab that shows my table of data. Example: This table, "WAGACT", is on a tab named "WAGACT". I just deleted all of the data from that table. This is the result of the query when it's refreshed: It should be completely blank but it's still pulling in information from somewhere. I just can't see where it's coming from or how to fix it. I have the following code: let FolderPath = Excel.CurrentWorkbook(){[Name="FolderPath"]}[Content]{0}[Column1], FileName = Excel.CurrentWorkbook(){[Name="FileName"]}[Content]{0}[Column1], Source = Excel.Workbook(File.Contents(FolderPath & FileName), null, true), WAGACT_Sheet = Source{[Item="WAGACT",Kind="Sheet"]}[Data], #"Removed Top Rows" = Table.Skip(WAGACT_Sheet,2), Do you see anything wrong with the code that could be causing this? or does anything else come to mind that I should check/change? When I go to the data source settings, it's only showing this, nothing external to the file I'm working with. I'm still very new to m code other than some work with formulas/columns so I greatly appreciate any help or advice. BridgettExpression error when changing a source from absolute to relative path in Power Query
I am trying to make a query source relative so that the workbook can be shared across users in our organization. I’ve followed several other tutorials and forum posts but am getting an error that I haven’t seen mentioned yet. The error being: Expression.Error: We cannot convert the value null to type Text. Details: Value= Type=Type This error appears at the Source in the applied steps. I added the formula =LEFT(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)-1) into cell A1 of a new tab in the workbook and then named that cell FilePath. Then, using the Advanced Editor, added 2 lines before the Source step. Just to be specific and clarify for this particular issue, GRNP is my table name and GRNP Trial Balance-BEFORE.xlsx is the filename of the workbook where the query resides. let FilePath = Excel.CurrentWorkbook(){[Name="GRNP"]}[Content]{0}[Column1], FullPathToFile1 = FilePath & "GRNP Trial Balance-BEFORE.xlsx", Source = Excel.Workbook(File.Contents(FullPathToFile1), null, true), #"Trimmed Text" = Table.TransformColumns(Source,{{"Column1", Text.Trim, type text}}), #"Replaced Value" = Table.ReplaceValue(#"Trimmed Text",null,"blank space",Replacer.ReplaceValue,{"Column1"}), I think the error is referring to the …..,null, true) in the Source step but I don’t know what how to fix it. I am using Excel 2016 with Windows 10 Enterprise. I’m still very much a beginner and the task I’m trying to achieve is still a little too far advanced for me troubleshoot on my own so I would appreciate whatever input you may have! Bridgett
