powerquery

Is there away in Excel to import a list of members of a Teams Site? I thought about perhaps using the Microsoft Graph API to do it. In my company, we create lots of Team Sites and in Excel I want a simple way of being able to drop down to choose people who are in that team. People get added...

I've got a large excel model and I start off by importing the data from a csv file via VBA. As some of my data has leading 0's that I don't want to lose and I need to retain the formatting on certain fields, I've used power query and VBA to come up with the following code that imports my data...

I am looking for a power query custom column formula that will do the equivalent to this Excel SUMIFS formula below =SUMIFS(Country_Variety_Sales[2023 TOTAL],Country_Variety_Sales[Secondary No],Country_Variety_Sales[@[Secondary No]],Country_Variety_Sales[Country],Country_Variety_Sales[@Country])

Hi, I have this report that i have to manipulate each week and i was trying to record the actions so i don't have to do the same laborious process every week. One part of the manipulation involves in transforming the table with power query so i can remove the format type of the whole column...

Hello! I feel this this should be simple but i just keep coming across problems.. I have a excel doc that uses Powery Query very simply to update a MASTER TABLE (one tab/table) every day. It pulls in fresh data every day (completely replaces the old data) and then I have millions of other tabs...

Hello. I am not sure if this is the right group for this question. Anyway, I was wondering if it's possible to run SQL query, using a column from an excel as parameter, on an excel file. for example: Select * from (excel column) a inner join (Sql.table.column) b on a.column1 =b.column2. I want...

Hi, Consider the attached image. I fetch a few 100k records from a database into SER_Base_SQL and MC_Base_SQL I add Table.Buffer() as the last line of code for each query Then I reference MC_Base_SQL in a new Query DueAssesments and perform some operations After closing the PowerQuery...

When the item-list is selected in the range in particular cell, the Shape button should be visible on the side. How it possible to make this hyperlinkbutton in table

Hello, I have the below code where I am trying to create a dynamic folder path, however it is erroring when I go to combine the content with "Expression.Error: We couldn't find an Excel table named 'FolderPathVariables'." The source files clearly do not have an excel table named...

Hello I have the following table and need to calculate the duration among users for each status changed (Open>Closed): USER TIME STATUS Tony 04/05/2023 08:45 Opened Mary 04/05/2023 08:55 Opened Tony 04/05/2023 09:24 Closed Mary 04/05/2023 14:06 Closed Alan 04/05/2023 16:07 Opened...

So I have PowerQuery cleaning some data and splitting it into different work groups. Works a treat, saves me mucho time and effort. But I am now getting greedy and want to push it further. Can I get PQ to: Split this into different workbooks instead of worksheets. That is rather than, say, 10...

I often find myself sifting through large amounts of data to identify relevant keywords, and as many of you may know, this can be a time-consuming and sometimes frustrating process, particularly when it comes to spelling errors. To address this issue, I came up with the idea of using PowerBI to...

I have a (successful) query that Groups Rows by a business unit name, and sums a few columns to give totals those units. In a previous step I had already filtered my data to just be one month so I could check my totals against the manual report we use. Now I have opened it up to all the dates...

Hi Excel Gurus, I am attempting to set up a spreadsheet where I can update data in tab 1 on a monthly basis, then refresh the pivot in (tab 2). In tab 1, when I paste the new set of data on top (same format), when I refresh the pivot (tab 2), my power query disappears - no data appears in the...

Hi All Only been dipping my toe into powerquery for a couple of weeks, and it is saving me so much time with my reports etc. Time to turn it up a notch or two. I have several queries (left-hand toolbar) and in one there is a column with dates. I want to find the newest date (which I think will...

Hi folks, I'd be grateful for any help with what should be a really simple set of PowerQuery steps - it's driving me mad as I just cannot get it right. Rather than (embarrasing myself by) pasting my current set of steps, let me show you what I have to work with (obviously the input document is...

I have a column containing broken/good domains I want to compare it to a list containing all the correct domains, and return those from this list that partial matches with [Email Domain]

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