Excel Forum » Office 365
87 FOLLOWERS
Excel Forum is an online forum where you can post any Office 365 questions.
Excel Forum » Office 365
1w ago
]
Hi,
I want the filter by text in a string for each column of a table so to filter by one column one word;
Code:
Table.SelectRows( Source, (A)=> not Text.Contains( A [One] , "cat") )
From this I did a manual version for each column ;
Code:
Table.SelectRows(
Table.SelectRows(
Table.SelectRows( Source, (A)=> not
Text.Contains( A[One], "dog") ), (B)=> not
Text.Contains( B[Two], "dog") ), (C)=> not Text.Contains( C[Three], "dog") )
and then to accumulate ;
L
Code:
ist.Accumulate( Table.ColumnNames( Source ) , Source, (S,C)=> Table.SelectRows( S, (B ..read more
Excel Forum » Office 365
1w ago
Hi
Wondering if anyone can help me here, I created a Microsoft list for recording overtime usage at my, I was on holiday last week and when I got back I could not access the list as I didn't have permission as I'm the owner I found this very strange, I did make a couple of colleagues owners as well but they have said they didn't do anything.
Today I noticed this email would this have something to do with me not getting access or has the MS list been corrupted in some way.
Image.docx
Thanks
Toddp2 ..read more
Excel Forum » Office 365
2w ago
Hi,
I want to change Lists and Recors to Strings in PQ.
I am receiving data from an API and I have an Array and an Object Column like this:
Code:
let
soure_tmp = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wio7JiykBQTgDCJUSk5JjlHSQRVJSgSIwgVgdrLoKgQBJEYQRq6SjVB2Tp6AQo5SWWVRcEp+XmJsao2QF5HvlZ+SBrQFJ5iSiyAXnZpZkwCUzi+MTczLLwHIlRaWpUOHEdLCIkXmtUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ArrayCol = _t, ObjecCol = _t]),
#"Convert" = Table.TransformColumnTy ..read more
Excel Forum » Office 365
2w ago
Hi!
I am trying to see if Excel is able to populate a cell with a text given that another cell has one of a predetermined list of texts put into it. For example, when "blue" is typed in one cell, the cell next to it will give "sky". I have a few hundred of these combinations (ie: red - apple, yellow - banana, etc) so I would like to avoid a really long IF THAN statement function if I can. Is Excel able to do something like this? Thanks!
Thank you everyone for your swift responses and help ..read more
Excel Forum » Office 365
3w ago
Hi,
I have sort of solved this but there may be a better / fewer step method,
I have a table where say customers have bought certain Items, and I want to know who bought All but not "A",
I have attached workbook with xl showing this;
My way round this was to filter the table for each item then merge with a left anti join, then on the resulting tables,
use merge inner join or convert to list and use list intersect.
So my questions are ; is there a better way of doing this and is it possible to add a true / false column,
so bought all but "A" = True,
Richard.
Attached Files
PQ A not B .xlsx ..read more
Excel Forum » Office 365
3w ago
Hello, I've attached an Excel Sheet that I've created and having trouble resolving the last formula in cell AL17. I have 4 stages of work "Disconnect / Uninstall","Vendor Shop Service","Shop Service / Verify" & "Reinstall / Reconnect " that show the progress of work. I need to track the total of how many jobs are ongoing in cell "AL17 (Total Ongoing)", and I can only attribute a count of 1 per job no matter how many stages of work are ongoing at the same time. Also there are other criterias that would disqualify a job from being counted and those are at the beginning of the formula. I woul ..read more
Excel Forum » Office 365
1M ago
Hi
I have a simple table with 3 columns
I run several filtering rules on the table (3 in this example) and get 3 filtered tables according to the parameters I defined
To simplify, I built a query that only creates the filtered tables.
In the next step I want to run a loop that will load all the filtered tables as an image into an existing sheet called "Rprt"
Is it possible to export a table as an image from query to the workbook?
If it is not possible to export as an image, how can you dynamically export all the tables to the workbook using a loop (the amount of rows and columns varies a ..read more
Excel Forum » Office 365
1M ago
Hi Community,
i want to automate a power query connection. In this case i use the following office-script:
Code:
function main(workbook: ExcelScript.Workbook) {
// Refresh all data connections
workbook.refreshAllDataConnections();
}
When I run the script manually, it does what it's supposed to do.
But when I create a cloud flow in PowerAutomate and want a scheduled execution of this script, it looks like it at first glance, but it doesn't work.
I found an article explaining that the workbook.refreshAllDataConnections() function is not ..read more
Excel Forum » Office 365
1M ago
hi, Apologies if this is in the wrong place. I am new here and to excel
i have a faculty list in sheet 1. i want to make employees information form using ID CNIC to fill the data in 2nd sheet please guide.template employees information.xlsx ..read more
Excel Forum » Office 365
1M ago
Any data transfer plugin available for Excel 365 Similar like (cwbtfcai.dll). This DLL do not work in new version of Excel. This is used when adding a COM Add-On ..read more