Forum Widgets
Latest Discussions
Dynamically changing ODC Connection
I have connected Excel to a Power BI dataset using ODC (Office Data Connection). I am pulling data into 19 different Excel tables, so I have 19 different ODC files all configured for the different columns, filters and sorts I need to pull the Power BI data. I am using these ODC files across 7 different Workbooks with some overlap, so sometimes the ODC is used across 3-4 different Workbooks, but generally only once. This all works fine. This solution HAS to be in Excel as these Excel files are used as templates for a Bulk Upload to one of our systems. However, the Power BI dataset has a PROD and TEST version and I have been asked to allow the Excel files to be “switched between”. So effectively there are two different Dataset IDs so there should be two different “Connection Strings” in the ODC file (or two different ODC files for each of the 19 versions). I can edit the ODC files in Notepad easily and search and replace the Dataset ID’s but trying to find a longer term more supportable method. Or I can edit in Excel and then resave another ODC to get the same result. https://learn.microsoft.com/en-us/office/vba/api/excel.oledbconnection.connection If I attempt to change the “Connection String” in Excel via Visual Basic it fails even though this is meant to be read/write. I also can’t seem to create a Parameter to pass the Dataset ID in as a variable either. Does anyone have experience in this or could make an alternative suggested method?
Importing CSV Data, Detection of Structure Inconsistent
I use the data import feature of Excel a lot at work and occasionally at home. When I do I use the PQE (Power Query Editor) by pressing the Transform Data button. I've run into a situation where I downloaded two files from a bank in CSV format. They are formatted exactly the same. The difference is the data found in the three columns. One file shows all three columns and correctly defaults to comma delimiter while the second file incorrectly defaults to colon delimiter and shows two columns. If I change the delimiter to comma the number of columns displayed is reduced to one. The first column has date and time and the colon in the time is what Excel is determining is the delimiter rather than the two commas that separate the three columns. From my perspective, this is a bug. The files are formatted exactly the same. The difference is the data contained between the commas. Neither file have quotes around the data for any of the columns.
Auto fill Department column
Good afternoon Microsoft Community, This is my first post ever with this site. I have the following project that I need help with. I need to auto fill the Department in column A in the Raw Data worksheet, with the DEPARTMENT name on worksheet Supervisor Drop-Down list. The Raw Data worksheet has Column F with the names of the current employees. I want a function/formula that will check for the names in the Supervisor's Drop-Down list and automatically add that department to Column A on the Raw Data worksheet. I have explore many different videos on the web and have not been able to find a solution. please advise on the best approach to handle this task.How to unprotect Excel sheet if forgot the password
I recently encountered a problem and hope to get your help. I set a protection password for an Excel file before. Now I want to modify some data, but I found that I forgot Excel password. I wonder if there is any way to remove the protection or unprotect Excel sheet password? If anyone knows a related solution or has had a similar experience, please share it, thank you very much! This file is very important to me, and there is a lot of work data in it. I have tried some methods found on the Internet, but none of them worked. It would be great if someone could provide some specific steps or recommend some tools.
Trying to create a scheduling graph
Hello, I am trying to create a scheduling graph. So, the schedule is made on one sheet, and that sheet is titled Schedule. On a separate sheet I have my graph. Let's say that boxes G5 and below is employee names and boxes H4-V5 are the times the business is open 8am-10pm. In the picture the graph is for Monday. I am trying to pull the scheduled shift of person BT from the Schedule sheet so that would be box E11 and then highlight the cells that correspond to that shift. So, it would highlight boxes H5-P5. I decided that I will do one graph for each day of the week. I just cannot figure out the formula to make it happen. I know you could manually highlight this but am trying to have it auto update so the person doing the scheduling can concentrate on scheduling. I was able to figure out the formula to calculate total employee hours took me a while, but I did it. Really having issues with this one. =SUM(IF(Schedule!D11:J11="OFF", 0, (TIMEVALUE(SUBSTITUTE(SUBSTITUTE(MID(Schedule!D11:J11, FIND("-", Schedule!D11:J11) + 1, LEN(Schedule!D11:J11) - FIND("-", Schedule!D11:J11)), "am", " am"), "pm", " pm")) - TIMEVALUE(SUBSTITUTE(SUBSTITUTE(LEFT(Schedule!D11:J11, FIND("-", Schedule!D11:J11) - 1), "am", " am"), "pm", " pm"))) * 24))Trying to Find/Replace Based on Another Sheet in Power Query
I have a list of URLs that have UTF-8 characters in them and I need to replace all of the UTF-8 characters with the actual characters (i.e. %26 to &). I've found two code samples from a separate thread to help me with this but I'm having trouble targeting the correct column (LinkURL) and am getting an error: The two code samples referenced above: // Variant1 ReplacedUtf = Table.ReplaceValue( Source, each [LinkURL], null, (old_value, current_value, replacer) as text => if current_value is null then old_value else List.Accumulate( ListReplacmentsTexts, current_value, (value,lists) => Text.Replace( value , lists{0}, lists{1} ) ), {"LinkURL"} ) // Variant2 ReplacedUtf = Table.ReplaceValue( Source, each if [LinkURL] is null then false else [LinkURL], each List.Accumulate( ListReplacmentsTexts, [LinkURL], (current_value,lists) => Text.Replace( current_value , lists{0}, lists{1} ) ), Replacer.ReplaceText, {"LinkURL"} ) This is the sheet with all of the encoding characters in it that I'm trying to reference: Full code for the AllContentReport as found in the Advanced Editor: Column I am trying to target I've tried switching out LinkURL in the code above to Column19 since the promoted headers step hadn't been executed yet but that didn't work either. Due to the nature of this content, I'm unable to share the workbook that I'm working with and, since it's referencing a connected workbook rather than a sheet found in this workbook, I didn't think creating a dummy data spreadsheet would help here. Any help would be greatly appreciated!
Data organized in Columns instead of Rows - Excel
Hi, In excel, I have a long list of data that I want to organized in 4 columns (First Name, Last Name, Phone and Email), the thing is that the data I have is in one column, so like in the image, the data that I have is organized downwards in a single column, as you can see in A1 is Kevin's first and last name, in A2, is Kevin's phone number and in cell A3, his email, the same for John and Angela,how can I place the information in 4 columns like it is in the table on the right? Thanks!
Help with Index/Match/Transpose Formula
I am trying to make a table where i have Bridal party roles and the qty of that role that is in the bridal party in to a single column with each role repeated based on the qty listed int he other table. Copilot helped me get this far but it still isn't quite right. I have a helper column in A2 =SEQUENCE(SUM(U11:U17)) and in B2 I have formula =INDEX(T$11:T$17, MATCH(ROW()-ROW($B$2)+1, MMULT(--(ROW(T$11:T$17)>=TRANSPOSE(ROW(T$11:T$17))), U$11:U$17), 1)) IT seems to be doing something close to what I want but it Best Man should only be listed once. Bridesmaid should be listed 4 times groomsmen should be listed 4 times etc.. Any help with my formula would be greatly appreciated. Thanks!
Extend Value to all Weeks going forward
Hi, My first post to community. I need help to extend value for all week endings from now. My Week ends on Saturday. First table is my current output from Power query where I have closing stock for each item as of week ending. As you can see there is no value for all week ends going forward. I want to get expected output table below as if there is no value for future weeks from now(), The value should be the previous Closing stock value. I can add the suggested solution as a last step in power query or as formula in excel. I have 960 different Items with this type of data.
