Forum Widgets
Latest Discussions
En Excel, expresión MIEMBROCUBO no reconoce conexión con el modelo de datos de POWER PIVOT
Por favor, solicito su ayuda sobre un inconveniente que estoy presentando actualmente con mi modelo de datos en Power Pivot y las expresiones CUBO, las cuales uso para crear informes dinámicos en Excel, que luego entrego a mis clientes. La situación es la siguiente: - Creo mi modelo de datos en Power Pivot de Excel, además creo medidas con expresiones DAX, y luego desde la hoja de Excel desde una celda invoco la expresión MIEMBROCUBO, la cual tiene dentro de su primer argumento "Conexión". Anteriormente, después de digitar la comilla " automáticamente aparecía el tooltip (tal como se muestra en la imagen adjunta) donde me mostraba las conexiones actuales vigentes en ese archivo (ThisWoorkbookDataModel), lo seleccionaba y continuaba configurando la expresión y no pasaba nada, todo funcionaba de maravilla. En estos últimos días, cuando iba a invocar una nueva expresión CUBO, ya no aparecían las conexiones disponibles en el archivo 😳 Cabe mencionar que al acceder a Power Pivot, el modelo se encuentra allí, las relaciones entre tablas también e incluso las medidas creadas. El día de ayer desinstalé Office 365, y luego lo reinstalé y registré la cuenta que siempre he usado (cuenta educativa). El día de ayer, luego de instalarlo, me permitió trabajar con ello, pero como que le hizo daño la apagada, porque hoy en la mañana volvió con el mismo problema 😔 ¿Pueden ayudarme con este inconveniente?
Функції
Доброго дня, маю питання що до функцій, є така таблиця, ліворуч зверху (червоним відмічено) є дата, також по середині є рядок з розбивкою по днях ця середня дата міняється згідно тієї дати що зверху, і є рядок із буквами Р,П,С,РС, потрібно хробити щоб у кінці автоматично виписувались дати (у тому стовпці де пише Період) згідно того де стоять букви Наприклад 2.12.24,15.12.24, стоїть буква "П" потрібно щоб у кінці автоматично записувались ці дати у другому стовпці жовтих стовпців так само із буквами "Р","С" щоб вони записувались у своїх клітинках, а букви "РС" додавались у стовпці де прописано"Р","С" до прикладу як вказано на другому скріншотіStacking row/columns of an array - some module formulas
I just wrote a few functions that should theoretically allow you to take an array, chunk it by rows or columns, and then stack them in the other dimensions, e.g. you have 10 rows by 3 columns and you want to stack them as a set of 5 rows by 6 columns. You should be able to use this to completely flatten an array (e.g. take each column and place them one under the other). It has optional parameters for separate fill values for rows and columns. I welcome any comments, suggestions, or alternative approaches. By way of contrast, if you had an array like: ctrl date end_date 1 1/1/2021 1/1/2021 2 1/15/2021 1/15/2021 3 2/2/2021 2/2/2021 Excel's native TOCOL function would flatten it into one column like this: =TOCOL(B4:D6) or =TOCOL(B4:D6,,FALSE) ctrl date end_date 1 44197 44197 2 44211 44211 or this =TOCOL(B4:D6,,TRUE) ctrl 1 2 date 44197 44211 end_date 44197 44211 whereas mine flattens it like this: =chunk.vStackChunksOfColumns(B4:D7,1) ctrl 1 2 date 44197 44211 end_date 44197 44211 or this =chunk.vStackChunksOfColumns(B4:D6,2) ctrl date 1 44197 2 44211 end_date 44197 44211 or this (which is effectively no change): =chunk.vStackChunksOfColumns(B4:D6,3) ctrl date end_date 1 44197 44197 2 44211 44211 hStackChunksOfRows = lambda( target_array, rows_per_slice, [no_columns_to_adj_slice_to_df_NO_ADJUSTMENT], [fill_value_for_added_columns_df_DBLQTS], [no_rows_to_adj_array_to_df_ROWS_PER_SLICE_TIMES_NO_OF_SLICES], [fill_value_for_added_rows_df_DBLQTS], let( rows_in_array, rows(target_array), cols_in_array, columns(target_array), no_of_slices, roundup(rows_in_array/rows_per_slice,0), target_slice_width, if( if( isomitted(no_columns_to_adj_slice_to_df_NO_ADJUSTMENT), FALSE, no_columns_to_adj_slice_to_df_NO_ADJUSTMENT ), no_columns_to_adj_slice_to_df_NO_ADJUSTMENT, cols_in_array ) , target_array_height, if( isomitted(no_rows_to_adj_array_to_df_ROWS_PER_SLICE_TIMES_NO_OF_SLICES), rows_per_slice * no_of_slices, no_rows_to_adj_array_to_df_ROWS_PER_SLICE_TIMES_NO_OF_SLICES ), fill_value_for_added_columns, if(isomitted(fill_value_for_added_columns_df_DBLQTS),"",fill_value_for_added_columns_df_DBLQTS), fill_value_for_added_rows, if(isomitted(fill_value_for_added_rows_df_DBLQTS),"",fill_value_for_added_rows_df_DBLQTS), adj_array, normalizeArray( target_array, target_array_height, target_slice_width, FALSE, fill_value_for_added_rows, fill_value_for_added_columns ), stack_iter, sequence(no_of_slices-1), stack_func, lambda( acc, slice_counter, hstack( acc, take(drop(adj_array,(slice_counter)*rows_per_slice),rows_per_slice) ) ), ifs( rows_in_array<=rows_per_slice, adj_array, rows_per_slice = 1, drop(reduce("",sequence(rows_in_array),lambda(acc, cur_row, hstack(acc, chooserows(adj_array,cur_row)))),,1), 1, reduce( take(adj_array,rows_per_slice), stack_iter, stack_func ) ) ) ); vStackChunksOfColumns = lambda( target_array, cols_per_slice, [no_rows_to_adj_slice_to_df_NO_ADJUSTMENT], [fill_value_for_added_rows_df_DBLQTS], [no_columns_to_adj_array_to_df_COLS_PER_SLICE_TIMES_NO_OF_SLICES], [fill_value_for_added_columns_df_DBLQTS], let( rows_in_array, rows(target_array), cols_in_array, columns(target_array), no_of_slices, roundup(cols_in_array/cols_per_slice,0), target_slice_height, if( if( isomitted(no_rows_to_adj_slice_to_df_NO_ADJUSTMENT), FALSE, no_rows_to_adj_slice_to_df_NO_ADJUSTMENT ), no_rows_to_adj_slice_to_df_NO_ADJUSTMENT, rows_in_array ) , target_array_width, if( isomitted(no_columns_to_adj_array_to_df_COLS_PER_SLICE_TIMES_NO_OF_SLICES), cols_per_slice*no_of_slices, no_columns_to_adj_array_to_df_COLS_PER_SLICE_TIMES_NO_OF_SLICES ), fill_value_for_added_rows, if(isomitted(fill_value_for_added_rows_df_DBLQTS),"",fill_value_for_added_rows_df_DBLQTS), fill_value_for_added_columns, if(isomitted(fill_value_for_added_columns_df_DBLQTS),"",fill_value_for_added_columns_df_DBLQTS), adj_array, normalizeArray( target_array, target_slice_height, target_array_width, TRUE, fill_value_for_added_rows, fill_value_for_added_columns ), stack_iter, sequence(,no_of_slices-1), stack_func, lambda( acc, slice_counter, vstack( acc, take(drop(adj_array,,(slice_counter)*cols_per_slice),,cols_per_slice) ) ), result, ifs( cols_in_array<=cols_per_slice, adj_array, cols_per_slice = 1, drop(reduce("",sequence(,cols_in_array),lambda(acc, cur_col, vstack(acc, CHOOSECOLS(adj_array,cur_col)))),1), 1, reduce( take(adj_array,,cols_per_slice), stack_iter, stack_func ) ), result ) ); normalizeArray = LAMBDA( target_array, final_height, // Desired final number of rows final_width, // Desired final number of columns fill_rows_first, // Should rows be filled first (default: TRUE) value_for_filling_rows, // Value to fill additional rows (default: "") value_for_filling_columns, // Value to fill additional columns (default: "") LET( // Get current array dimensions current_height, ROWS(target_array), current_width, COLUMNS(target_array), // Define row adjustment function (truncate or expand) row_func, IF( final_height <= current_height, LAMBDA(target_array_for_row_adjustment, TAKE(target_array_for_row_adjustment, final_height)), LAMBDA(target_array_for_row_adjustment, EXPAND(target_array_for_row_adjustment, final_height, , value_for_filling_rows)) ), // Define column adjustment function (truncate or expand) col_func, IF( final_width <= current_width, LAMBDA(target_array_for_col_adjustment, TAKE(target_array_for_col_adjustment, , final_width)), LAMBDA(target_array_for_col_adjustment, EXPAND(target_array_for_col_adjustment, , final_width, value_for_filling_columns)) ), // Apply adjustments adjusted_array, IF( fill_rows_first, row_func(col_func(target_array)), col_func(row_func(target_array)) ), adjusted_array ) );
Pictures inserted in cells disappear after closing and reopening a workbook
Windows 11 Home, 23H2, 22631.4317 MS Office L T S C Professional Plus 2024, version 2408, Build 17932.20130 Example: After saving, closing and reopening workbook, this is the result: I have tried all of the generic troubleshooting, because I have seen multiple people experiencing this issue - none of them work. 1. Display options for workbook are all checked (for object, show all) 2. Pictures are embedded, not linked to locally stored files - otherwise I wouldn't be able to retain them in online platforms (e.g. opening in Excel from Android, once storing in One-drive). Even if they were linked somehow, original files were never moved. When I upload the very same workbook in One-drive and open through any online platform (via browser - Excel Online, on Android - Excel app / Office 365 app) pictures are showing as intended. Problem occurs only on desktop app. I have already tried to reinstall MS Office, clean install, all updates - problem persists. I do not want any workaround solutions like: - using VBA scripts, - or inserting pictures over cells. as this is a proper bug and shouldn't require advanced skills from casual users. Inserting pictures over cells and embedding them manually - change size to fit into cell, set Move and size with cells is just partial solution - pictures will stay after closing and reopening, but you cannot refer to them properly - e.g. I want to have a result of X LOOKUP to be a cell with Picture inserted into cell (doesn't return a picture in cell if picture is placed above cell - doesn't matter if it is set to Move and size with cells. X LOOKUP with pictures inserted in cells works perfectly until I close and reopen locally on desktop app ( #UNKNOWN! everywhere), but continues to work perfectly in before-mentioned "online" platforms - though I am a bit more advanced user and lot of stuff I do can only be done in desktop app - as soon as I want to make some more advanced actions, I need to go back to desktop and all I get is a bunch of #UNKNOWN! where Pictures inserted into cells are supposed to be. Please solve this bug - it is very frustrating - I have lost straight 12 hours of my life trying to solve it, but I am at my wit's end. Thank you very much for reply.Query for Power query , Power pivot and Power bi launching
I am not able to run power query, power pviot and also power bi. If i choose file in data from excel to import file for power query it opens but immediately the closes and open a recovery file. If i want to open the power bi file the app opens for just 10 second but again the app closes. further if i want to click power pivot then in manage i get the error of no data model is abled to load. I have office 365 and windows 11 is installed. all updates are already done. Kindly help
Issues with populated by Power Automate Date and Time column in excel
Good day. I created a Power Automate flow that takes a date and time and populates them into an Excel spreadsheet. However, once imported, the date and time are recognized by Excel as text, not as date and time values. This persists until I select the cell, place my cursor in the formula bar, and press Enter. Only then are they recognized as date and time. I am using formatDateTime in Power Automate to export the date and time. Any help would be appreciated.
Create a dax to filter only last four hours and lookup a column
Hi Team, I have below sample data: We need to create a dax using below logics: filterHeatSense_Device table for last four hours(use 'UpdatedOn'(max&min datetime) column for this) Then look if the 'UserMode' contains 'heat' in all rows. If the above 2 conditions are true then return "Heating on Last 4 Hours" else blank. We tried to create the below logic but its not giving correct output: Last 4 Hours in Heat = var latestdatetime = max(HeatSense_Device[UpdatedOn]) var earliestdatetime = latestdatetime-4/24 var last4hours = CALCULATETABLE(HeatSense_Device,HeatSense_Device[UpdatedOn]<=latestdatetime && HeatSense_Device[UpdatedOn]>=earliestdatetime) var maximummode = MAXX(last4hours,HeatSense_Device[UserMode]) var minimummode = MINX(last4hours,HeatSense_Device[UserMode]) return if(and(maximummode="heat",minimummode="heat"),"Heating on Last 4 Hours",BLANK()) Ouput: When I bring in UpdatedOn column into visual to test the above dax, it retuns the whole 24hours instead of last four hours. I want to show only last four hours where there is heat in table. Could you please help us in creating a dax or modify the above dax ? PFA file hereHeatsense - Copy.pbix Please advise! Thanks in advance! SergeiBaklan
