Apply filters from table to Excel Power Query
I am looking to create an Excel Power Query to do the following:
- Add a new data column to my raw data based on filters from a different table
I have tried using the merge tables feature but it limits the filter to 1 column/criteria.
See also questions close to this topic
store numbers out of a text in an array using VBA macros and test it
I have the following cell contents as an example in an Excel worksheet:
commerce 81 commerce81 commerce1981 commerce 1981 law 2000 law2000 law00 law 00
I was looking for VBA code to store only numbers out of these texts in an array which I could do and figure it out using following code:
Function albilynumer(cell As String) As Long Dim c As Long For c = 1 To Len(cell) If IsNumeric(Mid(cell, c, 1)) Then albilynumer = albilynumer & Mid(cell, c, 1) Next End Function
But after separating number from text I want to test each to give me following result for previous example:
1981 1981 1981 1981 2000 2000 2000 2000
If any one has a clue for this I would be grateful to them.
create a folder based on textbox and save as excel sheet inside this foder C#
I'm creating a folder and a excel file every time i run my routine and its has been working just fine. But i cannot find a way to save this excel file inside my this specific folder i just created folder.
I would like to create my folder and save my excel file inside it, of course my folder name will be different every time.
any ideas? Thank you very much.
Thanks a lot in advance.
What I have tried:
Directory.CreateDirectory("c:\temp\" + foldername.Text);
sheet.SaveAs("c:\Temp\" + excelname.Text + DateTime.Now.ToString("_yyyyMMdd"));
How do I change the formatting of numbers in an array in VBA
I have written a short code that rounds some numbers in a table and stores them in an array object. I would like to set a custom format for these stored numbers, however I am not sure how, as the standard formatting method does not work with arrays.
My code looks like the following:
Dim i as long Dim x as long Dim RoundedAmounts (1 to 26) as long for i = 1 to 26 x= range("I44").offset(i,0).value if abs(x) < 150000 RoundedAmounts(i)=0 else RoundedAmounts(i)=Application.WorksheetFunction.Round(x, -5) end if Next i
I am a newbie at coding, so I'm not sure if this is an obvious task, but I couldn't seem to find an answer on Google. Any help would me greatly appreciated!
Remove duplicates from data downloaded from query in Excel using Excel VBA
I have made a query to download data from a website which has record of past 60 days data. But i need to store values of past 240 days after that whenever new data is fetched the oldest data should get automatically deleted. For that to happen i tried moving 60 days data(Originally recieved in A4:A63) to a new location (A184:A243) and cleared contents A4:A63 first.... then next batch of Data recieved in A4:A63 is moved to location (A124:A183) and cleared contents A4:A63...... then next batch of Data recieved in A4:A63 is moved to location A64:A123 and Cleared contents A4:A63. In this way 240 days of data is stored. After that whenever new batch of Data is fetched from website the latest data gets stored and the oldest one gets deleted its just like First in First out. Here is the code that i made
Sub ShiftData() Dim i As Integer If IsEmpty(Range("A4").Value) = True Then '<== To Check new data is present MsgBox ("No Input to shift") Else If Range("A" & 243).Value = "" Then '<== Data shifted to last place Range("A243:A184") = Range("A63:A4").Value Range("A63:A4").ClearContents ElseIf Range("A" & 183).Value = "" Then '<== If last place is occupied data shifted to lastmost empty place Range("A183:A124") = Range("A63:A4").Value Range("A63:A4").ClearContents ElseIf Range("A" & 123).Value = "" Then Range("A123:A64") = Range("A63:A4").Value '<== If last place is occupied data shifted to lastmost empty place Range("A63:A4").ClearContents End If If Not IsEmpty(Range("A4:A63").Value) = True Then If Range("A243") <> "" And Range("A183") <> "" _ And Range("A123") <> "" And Range("A63") <> "" Then '<== To check if all places are filled. Range("A243:A124") = Range("A183:A64").Value '<== Shifted all data to flush out oldest data Range("A123:A64") = Range("A63:A4").Value Range("A4:A63").ClearContents End If End If End If End Sub`
The problem i am facing here is that with every feed from website i get only one to four rows of new data i.e 60th day rest of the data (59 days) is same. So if i stack like this i am getting multiple duplicates which is creating problem in my analysis. I want to get only fresh data to stack up and ignore duplicate data. Let me explain this with an example taking 3 new rows at a time so as to make easy understanding. enter image description here
Power Query Sum of column by group as new column
So I am new to power query and I just wasted over an hour looking for something that I can do easily in many other programs.
I just want to create a new column summing up another column. FOr instance, to check if the percentage a correct and if not normalize therafter. I dont want to group by and reduce the table.
I ve been searching left and right and tried to add a new column like "Group Sum" using stuff like = list.sum([Number]) = Calculate(SUM([Number])
just to get the the total sum of all entries 200. No success.
Maybe its me, but I really dont see the logic.
Fetching Data from excel file using Excel power query(Get Data > From File > From Folder)
I am trying to pull data to an Excel sheet (to make Power pivot) using power query. But whenever it starts fetching the data it shows this error after few seconds:
The operation failed because the source database does not exist, the source table does not exist, or because you do not have access to the data source. More Details: OLE DB or ODBC error: [Expression error] The key didnt match any row in the table. An error occurred while processing table 'data'. The current operation was cancelled because another operation in the transaction failed.
The files are present in the specified folder, also i have full access (read/write) to the folder and the files.