powerquery

I am trying to check if there are any exact matches between [Email Domain] (contains a single domain) and [All Matches] (contains a bunch of domain). However, it keeps returning the error below Initial State \ Code = Table.TransformColumns(#"Grouped Rows", {"All Matches", each let...

I am confused why my Custom Column returns a Function instead of lists? Output: = Table.AddColumn(#"Grouped Rows", "Test", each if List.FindText(Text.Split([All Matches], ","), [Email Domain]) <> false then each let EmailDomainVar = [Email Domain] in List.Select(Text.Split([All Matches]...

If I use Text.Contains to have 1 filter only, it works fine (e.g. only those containing "@"). However, I want to combine the filters into 1 query so there won't be so many steps. Therefore, I'm using List.ContainsAny instead. I think it's because PQ cannot pick up List.Accumulate's "current" if...

How do I convert each row into a list without adding a new column? What I have so far: = Table.TransformColumns(TESTT, each Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)([TESTT])) It gives error: Expression.Error: We cannot convert a value of type Function to type List. Details...

I'm trying to check each list if any of its content matches any other lists' contents, and then flag them if found e.g. for each List in Time if List.ContainsAny(Current List, Any List from time column) then flag repeat for all...

I want to identify duplicates in column RandomColumn without using Group By - as this method will drop unused columns = let columnNames = {"RandomColumn"}, addCount = Table.Group(#"Added Index", columnNames, {{"Count", Table.RowCount, type number}}) in Table.AddColumn(#"Added Index"...

I have a column that contains lists of dates. How do I remove dates from those lists where value does not = x?

= Table.AddColumn(Source, "Custom", each {Date.From(Date.StartOfMonth)..(Date.EndOfMonth)}) I am trying to create a custom column but I keep getting this error:

Hello, I'm new to PowerQuery and loving it so far. I have some ETF prices I pulled from Yahoo Finance and pulled into PowerQuery. I have manipulated it into the following (Table 1): I want the data to look like the following (Table 2). Essentially, I want the "Ticker" column to instead by...

directory: X:\Case study - 11\Code\Reports Step 1: I am fetching the data from the pdf (filename:ceds) in excel by doing this: "Data -> get data -> from pdf". Step 2: I then obtain 7 different sheets in a current excel file. Step 3: I want to save this excel file in a folder(folder...

Hello, What I am attempting to get help with is creating an automated power query (as in using a macro button to initiate process) that loads and transforms the source file into a usable and repeatable format. Below, I have attached a picture of the source file so that you can see what it looks...

Hello, I don't knowmuch about VBA but searching on the internet I could find some macros that help me do what I need. I have a power query bringing some emails to my sheet, I need to have the sheet refreshed every minute and the native function in excel does not work when more than one person is...

Hi , I have been able to create my first dynamic path linked to a folder. Still working to understand the logic involved in the the creation process. My dynamic path seems to work but only if I update the name of the file in the advanced editor but I have seen people simply dragging and drop...

Hi all, I am fairly new to Power Pivot. I am having some trouble understanding filtering between related tables. I have my main table "Demographics", in which "Index" is Unique so: Index Role 1 Engineer 2 Engineer 3 Manager...

Hi all, I am fairly new to PowerQuery. I have 3 columns, each one with a Table (all tables have the same structure and number of rows): When I expand Column 1, every row of the expanded table gets a table for Column 2 and 3, So I get a big number of duplicates. Is there any way I can...

Hello All I am trying to find all unique combinations of a list of words. The list would ultimately consist of 20-25 words. Example (with small sample): Input: A B C Output: "A B C" "A B" "A C" "A" "B C" "B" "C" I have tried using VBA, but as soon as the amount of words exceeds 5 it is...

Guys, here's my issue.... I have this data grid that I need to pivot it. Simple. The problem is that the dates comes across (toward the right) of my main columns. Usually, I'd rearrange my data in a vertical rather horizontal direction BUT It cannot be moved as per client's request. Well...

Hello, I am reaching out to anyone for guidance on this one. Here is the detailed scenario I have a Sales Commission Report in excel. There are 5 worksheets plus summary sheet Each Month we add the new onboarded client under reps name for 90 days Looking to have Summary sheet automatically...

Hi all, I have a raw data that stores invoices per month. Next month will add 2 more columns for invoice for that specific month (see below example of my raw data). Column D and E - invoice for June 2021 Column F and G - invoice for July 2021 ABCDEFG1CountryNovationDateRegionPI Value...

Hi Pros, I have been trying to split address from a cell to multiple columns. but I have to create a step every time I have to split which has different delimiter. However I would want this to be one step where it identifies different delimiter and split it accordingly to multiple columns...