Powershell to refresh Excel with OLAP Query. Credential issues
Hi Experts, I'm rather new with PowerShell scripting so please be patient with me 🙂 I have multiple excel file with OLAP Query connections connecting to Power BI Datasets, following are the script; $libraryPath = "C:\Repos\AUSD\3.0\Test" $excel = new-object -comobject Excel.Application $excel.Visible = $false # Give delay to open Start-Sleep -s 3 $allExcelfiles = Get-ChildItem $libraryPath -recurse -include “*.xls*” foreach ($file in $allExcelfiles) { $workbookpath = $file.fullname Write-Host "Updating " $workbookpath # Open the Excel file $excelworkbook = $excel.workbooks.Open($workbookpath) $connections = $excelworkbook.Connections # This will Refresh All the pivot tables data. $excelworkbook.RefreshAll() # The following script lines will Save the file. $excelworkbook.Save() $excelworkbook.Close() Write-Host "Update Complete " $workbookpath } $excel.quit() It is working fine if following options; $excel.Visible is true However this is going to be scheduled in the server and hopefully this could be done in the background, hence the $excel.Visible = $false This causing the following error; I suspect this is due to the Automatic sign in which happen when the Excel are open, due to its not being open, its failing the sign in process. -------------- Here come the question, how do I bypass or rather set the credentials/permission right?What’s Included with Microsoft’s Granted Offerings for Nonprofits?
Are you a nonprofit looking to boost your impact with cutting-edge technology? Microsoft is here to help! From free software licenses to guided technical documentation and support, this program offers a range of resources designed to empower your organization. In this blog, we’ll dive into the incredible tools and grants available to nonprofits through Microsoft, showing you how to make the most of these generous offerings. Whether you’re managing projects or just trying to simplify your day-to-day tasks, there’s something here for everyone. Let’s explore what’s possible!
Sharepoint Excel doc not receiving data from Form
I created a Sharepoint page that has an Excel doc with a Form in the document which looks like a survey for over 50 people in our company to fill out requests. The Form is embedded in the Sharepoint home page. Using a modified "Notify the Team" Flow, once the request is submitted through the survey that triggers an email to the person who submitted so that they have a record, and that data then populates the Excel doc within the Sharepoint page. Everything has been going smoothly for the past 2 months, until last week when data that is submitted no longer populates the Excel doc. However, the flow history shows that the flow successfully ran. It's as if the data is getting "stuck" in limbo. When I submit a dummy request afterwards, that "pushes" the stuck data into populating the Sharepoint doc. Is there a way to fix this? Is a connection broken? Is this the appropriate place to ask? I appreciate any help. Thanks!
Open a link in Excel with the desktop software
Hey everyone! I'm looking for a way to open a link from an excel documents to a desktop software. As you can see on the screenshot: - I have an excel documents with a link inside it - This link is going to a document in a teams folder. It's a pdf documents. I want teams to open this link automatically with acrobat reader. Is there a way to do it ? I don't want to download it on my computer. Thank you in advance, Best,
Transform data in excel
Hi Recently, my client has shared raw data, which I have processed a bit (attached here for reference). The workbook has 2 sheets: 1. Raw Data 2. Converted Data I have data in the format provided in Raw Data sheet and want to get the data in format provided in Converted Data sheet. Is it possible in excel to get the data in the desired formatMicrosoft Excel Power User Updates | Agent Mode, Copilot Function & Formula AI
With Agent Mode, automate complex analysis, create pivot tables, and build interactive dashboards without manual setup. Streamline text analysis, formula generation, and complex calculations right inside Excel. Use the Copilot function to categorize feedback, score sentiment, or automate repetitive tasks, and leverage Formula AI to generate accurate formulas from plain language prompts. Jeremy Chapman, Microsoft 365 Director, shares how to work smarter and make faster, data-driven decisions in Microsoft Excel. No manual setup required. Auto-analyze your spreadsheets, generate KPIs, pivot tables, and charts. Check out Agent Mode in Microsoft Excel. Analyze text, categorize feedback, and score sentiment. Turn manual data tagging into instant AI-powered insights inside your spreadsheet. See how to use the =COPILOT() function. Save time and stay in flow. Generate formulas faster, understand what they do, and complete complex calculations with confidence. Take a look. QUICK LINKS: 00:00 — Excel and Microsoft 365 Copilot updates 01:24 — Agent Mode 03:55 — Copilot function 06:02 — Formula completion 07:13 — Formula AI 08:41 — Wrap up Unfamiliar with Microsoft Mechanics? As Microsoft’s official video series for IT, you can watch and share valuable content and demos of current and upcoming tech from the people who build it at Microsoft. Subscribe to our YouTube: https://www.youtube.com/c/MicrosoftMechanicsSeries Talk with other IT Pros, join us on the Microsoft Tech Community: https://techcommunity.microsoft.com/t5/microsoft-mechanics-blog/bg-p/MicrosoftMechanicsBlog Watch or listen from anywhere, subscribe to our podcast: https://microsoftmechanics.libsyn.com/podcast Keep getting this insider knowledge, join us on social: Follow us on Twitter: https://twitter.com/MSFTMechanics Share knowledge on LinkedIn: https://www.linkedin.com/company/microsoft-mechanics/ Enjoy us on Instagram: https://www.instagram.com/msftmechanics/ Loosen up with us on TikTok: https://www.tiktok.com/@msftmechanics Video Transcript: -If you’re using Excel with Microsoft 365 at work or school, it just got better. Now within Excel, Microsoft 365 Copilot brings more powerful AI directly into your spreadsheets. First, Agent Mode brings advanced agentic AI reasoning to your open files to help you analyze, apply formulas, create visualizations, and more to reach your intended outcomes. Then the =COPILOT function is a new formula that brings AI directly into your cells. Next, Formula AI makes it easy to find and use the right formula with automatic formula completion as you start typing and even natural language formula creation so that you can just describe what you want to do without knowing the formula name, and Copilot suggests the right one. -So most of what I’ll show today does need a Microsoft 365 Copilot license. That said, if you don’t have a license, and if your work or school uses almost any version of Microsoft 365 or Office 365, you’ll find Copilot in the home ribbon and can start using Copilot Chat with Excel on the web or the updated desktop apps right now. Now these are designed so that you don’t need to leave Excel and use an AI website, or even worse, upload your work files into AI tools that might not be safe or might not be managed by your company. -So now let’s dig in deeper into the updates. So starting with Agent Mode, which brings agentic reasoning directly into your spreadsheets, it can use Excel table structures, formula syntax, dynamic arrays, PivotTables, charts, and more to create workbooks that can be updated, refreshed, and verified. Let me show you an example using Excel on the web. So here we’re running a global B2B bicycle business, and I would need to generate a financial report. I’ve already opened Agent Mode from Copilot in Excel. Now I’ll type, “Run a full analysis on this data. Find important insights for making business decisions and create charts to help visualize insights.” And Agent Mode begins reasoning through the task. And you can watch its reasoning logic as it works through all the different tasks. It’s planning workflow steps, workbook structure, creating PivotTables, building a dashboard, and working through all the pieces of the dashboard. -Now depending on the job that you give Agent Mode, it can take a few minutes to generate its response. This took a bit over three minutes in my case. When I move back to its output, you’ll see the completed reasoning steps taken in the right pane, and I can see that it’s created a new sheet called Insights Dashboard. So I’ll open that, and it looks pretty visual. And with the sheet open, I’ll go ahead and move back into the Agent Mode output on the right, and you’ll see that it’s found insights in the data. So it’s found headline KPIs for sales, profit, units, and margin. It’s found segment profitability and discount impact. It’s also looked at my customer feedback, and we’ll dig in deeper into that in a moment. And it provides a few recommendations of where to focus on for the highest profit. -So it looks like we might be granting too many discounts and that we have a few seasonal spikes as well. Then it explains how it produced everything. And moving over to the dashboard itself, you can see this is all live data with references to the source content. It’s created PivotTables that you can edit if you want to, and here’s another one. And below that there are PivotCharts showing all of its work, just like I asked for, and it’s fully interactive. So you just need to tell it what you want, and Copilot builds, then evaluates, and iterates until the outcome is generated and verified. And even though I stopped in my case after one prompt, of course, you can also continue your conversation with Copilot until it builds exactly what you want. -Next is the =COPILOT function. This is a brand-new formula that takes Copilot AI right into the individual cells of your spreadsheets. This is designed for text-based analysis, and let me show you. In this case, we’ve received written feedback about various replacement bike parts that we sell. In the past, you might read each one and then tag every comment manually with a sentiment score or a category. Let’s have Copilot do this. So using the =COPILOT with a prompt of, “Rate the sentiment of this feedback as negative or neutral or positive,” and then the corresponding cell, H2, I’ll hit enter. -And here I’m using a single prompt and cell for context in this case, but I could use more parts. And if I drag this formula down, Copilot rates each comment by sentiment, whether it’s positive, negative, or neutral, and it enters the results. And this isn’t just a one-time operation because it’s part of Excel’s calculation engine. As you can see, if I make this positive comment here negative, and I’ll add another negative word here, then commit the change, the result in the cell updates automatically. -Now, if I wanted to categorize these lines of feedback based on the feedback categories here in my spreadsheet, I can use multiple parts. So this time I’ll type =COPILOT, then, “Categorize this feedback from” with the feedback cell again, H2. In the second part, I’ll complete the thought and say, “with the best matching option only from these feedback categories,” then choose the cell range with L$2:L$7 as absolute row references. Then I’ll hit confirm. Here Copilot uses my two prompts and cross-references the context range of feedback categories to generate its output. And these all look really accurate. So you can use the =COPILOT function for common text-based analysis right inside your workbook, and because it’s an Excel function, it persists and can even be nested in other functions too. -And that’s just one formula, and there are hundreds of others in Excel where even the best power users don’t know every single one. And that’s where the new formula completion helps you choose the right formulas using the context around your data to form a recommendation. I’m in Excel on the web, and I’ll type = in a cell. Copilot analyzes the context, the headers, the nearby cells, tables, and suggests a formula. For example, if I’m calculating year-over-year growth, because the column name here is YoY%, Copilot automatically suggests = /the last year again, D7. And it even shows a preview of the result as a percentage and a natural language explanation of what the formula does. -Now this output looks really good to me, and from there, I can just drag this formula down to the rest of the cells that I want to fill in down to total assets in this case. And I’ve got the year-over-year changes as percentages everywhere. This is even great for complex formulas, dynamic arrays, and REGEX patterns. You just need to type the equal sign, and Copilot will help you figure out what to use. And if you tend to know the formulas that you do want to use, well, from Excel options, you can always opt out of formula completion and select for how long. -Now let’s get back to the basics where you might not know where to start or what formula to use. And for that, from a blank cell, you can just use formula generation with natural language to describe what you want. Copilot then uses its language understanding to help. In this case, I have another sheet with global inventory levels for my bikes and parts, and I want to find out the inventory levels for the Trailhawk and the Roadhawk bikes in Europe. All I need to do is type =. Then I see a free text field with “Generate a formula that…” But in my case, I’ll describe what I want, so I’ll say, “Calculate the total number of Trailhawk and Roadhawk bikes that are available in warehouses located in Europe,” and the model knows which cities are in that area of the globe. -Then it generates a formula using SumIfs with the columns I want in range, B for the Trailhawks and A for the cities, repeats the same for the Roadhawk in column D. Then for the A column criteria, it lists out Dublin, Berlin, London, Paris, and Madrid as cities in the same geographical area. In fact, if I select each of these cells manually, first for Dublin, then Berlin, then London, then Paris, and all the way on the bottom, the Madrid row with columns B and C, you’ll see the total is 845. And this is still a relatively simple formula, but it might not be that easy if you’re new to formulas. -Those are just a few updates for how Copilot helps make Excel more powerful, whether you’re a power user or just getting started. Try out today by clicking the Copilot button in the Excel ribbon and as you add formulas right in your spreadsheet cells. And be sure to subscribe to Microsoft Mechanics for the latest AI tech, and thanks for watching.May the Power Be with You with Power Query in Excel (With Instructional Video)
Data cleaning is a crucial step in the data analysis process, but it can be time-consuming and tedious. Fortunately, Power Query in Microsoft Excel offers a powerful solution to automate data cleaning, saving you valuable time and effort. In this blog, we'll explore how to use Power Query to streamline your data cleaning process and ensure your data is ready for analysis.In this blog, we'll explore how to use Power Query to streamline your data cleaning process and ensure your data is ready for analysis. To complement the blog, we've also included an instructional video that walks you through the key steps, making it even easier to follow along and apply what you learn. Instructional Video: Blog: What is Power Query? Power Query is a data connection, cleaning, and shaping technology that is part of the Microsoft modern analytics suite of business intelligence tools. It allows you to connect to various data sources, transform and clean the data, and load it into Excel or Power BI for further analysis. Benefits of Using Power Query Automation: Once you set up your data cleaning steps, Power Query can automatically apply them every time you refresh your data. Efficiency: Power Query simplifies complex data cleaning tasks, reducing the time and effort required. Consistency: By automating the process, you ensure that your data cleaning steps are applied consistently every time. Getting Started with Power Query Step 1: Launch Power Query and Connect Data To launch Power Query in Excel: 1. Open Excel and go to the "Data" tab. 2. Click on "Get Data" and select the data source you want to connect to (e.g., Excel, CSV, database). For this demo we will use From File > From Excel Workbook to connect to a single Excel source. 3. This process launches a Windows Explorer window, where you can navigate to the source data's folder and select the workbook to Import. Power Query supports a wide range of data sources, including Excel files, CSV files, databases, and online services. Select your data source and follow the prompts to connect to your data. 4. The Navigator window launches, showing the selected file's contents. Most data sources follow this general layout, with different types of objects represented by different icons. For an Excel workbook, the object types include worksheets and tables. 5. Select the Transform Data button. This selection launches the Power Query editor window. Step 2: Clean and Transform Your Data Once your data is loaded into Power Query, you can start cleaning and transforming it. Here are some common data cleaning tasks you can automate with Power Query: Remove Duplicates: Identify and remove duplicate rows from your dataset. How to: In the Power Query Editor, select the column with duplicate values, go to the "Home" tab, and click "Remove Duplicates." Filter Rows: Filter out unwanted rows based on specific criteria How to: Click on the filter icon in the column header and select the criteria to filter out unwanted rows. Replace Values: Replace incorrect or missing values with the correct ones. How to: Right-click on the column with incorrect values, select "Replace Values," and enter the old and new values Split Columns: Split a column into multiple columns based on a delimiter. How to: Select the column to split, go to the "Transform" tab, and click "Split Column" > "By Delimiter." Choose the delimiter and click "OK." Merge Columns: Combine multiple columns into a single column. How to: Select the columns to merge, go to the "Transform" tab, and click "Merge Columns." Choose a separator and click "OK." Step 3: Apply and Save Your Changes After cleaning and transforming your data, click "Close & Load" to apply the changes and load the cleaned data into Excel. Power Query will save your steps, so you can refresh the data and reapply the same cleaning steps automatically. Conclusion: Power Query is a powerful tool that can automate and streamline your data cleaning process, making it easier to prepare your data for analysis. By following the steps outlined in this guide, you can save time, ensure consistency, and focus on deriving insights from your data. Start using Power Query today and transform your data cleaning workflow!
