powerquery

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...

Hi all, I am currently downloading a bunch of sheets that look like this: I would like to make an overview in my sheet called "List", where the data from "Sheet1" is automatically pulled in. However, I would like to do this without making any changes to the downloaded sheet, including not...

Hi, I have a question to all M experts - I would to make, in one single step, multiple custom columns like this Statement : = Table.AddColumn(#"Reordered Columns", "XXXXX", each if [XXXXXXt] = "XXXX" or "XXXX" then Value.Subtract(each [XXXX] - [XXXXXX.XXX]) else null) Do you know a way ...

Hi team, Can you please help me find a solution to the below issue? I tried Vlookup, but it is really time-consuming. I have two sheets. One with the contact details and one with the product details. I want them to map it based on the account data. Sheet 1: Sheet 2 Result required ...