power query

97 Topics

Apply Conditional Formatting to a Column and am seeking assistance
Hello All, I would like to apply conditional formatting to a column and am seeking assistance. My goal is to enable users to select a color that will automatically be applied to a column header in Power BI, based on data sourced from Excel. For instance, if a user selects '1', the color should be green; for '2', blue; and for '3', red. Please be aware that the report is updated monthly. Therefore, as users change values in Excel each month, these changes should be dynamically reflected in Power BI. I'm facing a challenge with applying conditional formatting to a column instead of a row. If you have a more effective way to model the data, your suggestions would be greatly appreciated. My brain is a bit fatigued, just want to see column conditional formatted.😁 Can anyone provide a solution for this? I have included a screenshot below for better clarity.
Solved
PBIfolks
4 months ago Place DAX Commands and Tips
5.1KViews
0likes
4Comments
Grouping Values by Multiple Criteria in several Tables (DAX or Power Query and big Flat Table)
I have don’t this in the past with data from our ERP with Power Query when it was one big flat table. However, I am hitting a big wall trying to do this in Power BI with many related tables. I need to create some groupings in Power BI for tables and charts. I have looked at several examples, but most seem to have a less complicated data model. I had considered Power Query, but I need to kook at multiple related tables and did not want a massive flat table again. The DAX I saw was confusing and there seemed to be some cautions. I have 5-7 different groupings (A, B, C, D, E…). I have actual expense and budget tables. In order for an amount to be summed into one of the categories it must meet several criteria that are in different related tables. It must Be in a certain Account Rage Be in certain Cost Centers, Be a specific Project Type Output might look like this Category 2025 Actuals YTD 2025 Projection 2026 Budget 2027 Budget 2028 Budget A 14143 14134 326 3453 433 B 2356 354 3533 5353 3452 C 3423 3497 3534 4345 3455 D 2324 2345 3533 3535 5352 E 2567 2599 5632 3567 3572 I can create a single measure to do one category but, I am really after either a “real” table via Power Query or a DAX query. Below is an example a measure if I were creating category A as a single measure for Actuals. Help ang guidance from multiple angles would be appreciated. Thanks Alan For example, for Category A measure might look like this, unfortunately I need that as a “row” not a new column. A = VAR _Amount = CALCULATE ( SUM ( 'Actuals'[Amount4] ), KEEPFILTERS ( VALUE ( 'Account Relations'[Head Account] ) >= 50000 && VALUE ( 'Account Relations'[Head Account] ) <= 69999 && VALUE ( 'Account Relations'[Head Account] ) <> 56950 ), KEEPFILTERS ( VALUE ( 'Account Relations'[Account] ) <> 96910 && VALUE ( 'Account Relations'[Account] ) <> 96950 ), KEEPFILTERS ( VALUE ( 'Main Project Master File - PMF'[PMF Project Type] ) >= 30 && VALUE ( 'Main Project Master File - PMF'[PMF Project Type] ) <= 69 ), KEEPFILTERS( VALUE('Department Relations'[Cost Center]) >=10 && VALUE( 'Department Relations'[Cost Center]) <= 20 ) ) RETURN _Amount
Solved
asjones
10 months ago Place DAX Commands and Tips
1.2KViews
1like
5Comments
Index Column Based on Filtered Data in Table Visual
Hi I have a table visual with three columns of data in Power BI, and I would like to add an index column that starts from 1 and increments by 1 up to the number of rows in the Name column. Specifically, when the data in the table is filtered, the index column should update to reflect the number of rows in the filtered result. Name- calculated column from app table and contains duplicates Age- measure City- measure Index- start the sequence from 1 and ends at the no of rows of the name column in the table visual. Duplicates should not effect the sequence. Sample Data (Before Filtering): Index Name Age City 1 John 25 New York 2 Alice 26 London 3 Bob 22 Tokyo 4 John 28 Berlin 5 Michael 15 Sydney Sample Data (After Filtering for "Age > =25"):we use slicer for the filter purpose Index Name Age City 1 John 25 New York 2 Alice 26 London 3 John 28 Berlin As you can see, the index should reset based on the filtered data, providing a new row number for each filtered result. Could someone suggest the best way to implement this dynamic index in my Power BI report? Thank you!
Solved
SBC
1 year ago Place DAX Commands and Tips
1.1KViews
0likes
1Comment
Consolidar xon power query con diferentes archivo con inicio de mes distinto
Tengo un caso, estoy consolidando 40 archivos en excel con power query en Power Bi, los archivos mantienen la misma estructura y formato por ejemplo tiene código de cliente líneas de gasto mensualizado mostrado en cada columna, enero febrero, marzo, etc., el detalle es, los archivos no inician en el mismo mes, el archivo 1 empieza de marzo del 2024 y termi a en maezo 2025, el archivo 2 inicia en febrero 2024 y termina en febrero 2025, otro inicia en Junio 2024 y termina en junio de 2025, así sucesivamente, cuando consolido y luego dinamizo para hacer una tabla corta toma el periodo del archivo 1 y no respeta los inicios de los otros ada archivo.
Anonymous
1 year ago Place Translated Spanish Desktop
273Views
0likes
0Comments
Powe Query - How do get the ttl count of the column is >0?
Hi All, i have a table as follow and would like to add a column custom column (TTL Item as follow) in power query that counts all the rows that are >= 1, i have found multiple formulas but they didn't calculate correctly. Can you please help? Name Apple Banana Grapes TTL Item John 1 2 2 Mary 2 1 David 1 3 4 3 Tom 1 1 2
Solved
iekiekiek
2 years ago Place DAX Commands and Tips
621Views
0likes
2Comments
Help with slicer and measures with DAX (maybe)
Hi All I hope you can help me with my problem. I'm still relatively new to Power BI even if I've been lurking here for a while so please bear with me. Context: My model have a table that contains Department and Location columns and another survey results table that is joined by location. The department column contains two departments (Farming and Processing) while my location column have (Cashew Farming, Hazelnuts Farming, Hazelnut Processing, Cashew Processing, Pistachio, Processing etc.). I am using calculated measures from survey results to give me a percentage figures that I show in a table (for example the one below). I am also using a slicer that uses the departments (Farming and Processing). Problem: What I am trying to do now is if I select any department from the slicer (Farming for example), I would like the values to show only the ones for Cashew Farming, Hazelnut Farming, Pistachio Farming and if I change the selection to Processing, it will only show values for Cashew Processing, Hazelnut Processing, Pistachio Processing. Location Great Good Bad Cashew xxx xxx xxx Hazelnut xxx xxx xxx Pistachio xxx xxx xxx I hope that makes sense? I have a feeling that this is a DAX solution but I'm not sure where to start. Appreciate your help in advance! Thank you.
Solved
rem_time
2 years ago Place Report Server
1.5KViews
0likes
6Comments
Custom Slicer based on Grouped Cell Values in a column
Hello, I am trying to create custom slicers that allow me to select a "category" instead of a cell value. This is what I have currently: a b c a1 b1 c1 a2 b2 c2 a3 b3 c3 a4 b4 c4 a5 b5 c5 a6 b6 c6 Suppose b1, b2, b5 belong to "Category 1", b3 and b6 belong to "Category 2", and b4 is "Category 3". I want to create a slicer that has 3 options: Categories 1, 2, and 3, such that it filters my table based on the selected category/categories. For example, if I select Category 1, the output should be: a b c a1 b1 c1 a2 b2 c2 a5 b5 c5 How can I achieve this? Thank you. Really appreciate your help!
Solved
Anonymous
2 years ago Place DAX Commands and Tips
772Views
0likes
3Comments
[Error] Couldn't load the data for this visual in DirectQuery mode
Hello, we are currently encountering an issue regarding data in the "Direct Query" mode: Within the Slicer: If it's single selection, the visualization chart encounters the following error: [OLE DB or ODBC error: [Expression.Error] Unable to fold the expression to the data source, please try a simpler expression] (as shown in picture below). If it's multi-selection, the visualization chart displays correctly. May I ask what could be the cause of this issue?
tiffany1234
2 years ago Place Custom Visuals Development Discussion
840Views
0likes
1Comment
Count of number of times value is present and create a custom column
In PowerQuery I want to add a custom column that evaluates how often a value exists and create a custom column based on the value For example, new custom column(No of Rows) should be created based on how many times value exist in Column1 and if it is greater than 1 it should display as Yes or No in the column Result Column The below screen shot is what I'm trying to create I dont want to create any group/summarized table
Solved
Anonymous
2 years ago Place Power Query
2.2KViews
0likes
3Comments
Calculate TRIMMEAN of last 12 months from raw sales data
I have a table containing quantity of each stock item sold per invoice. This includes date and I have a related Date table. I need to sum the quantity sold for each stock item monthly for the last 12 months then calculate the TRIMMEAN of this monthly data where high and low months are excluded. Do I need to create a new table for the rolling 12 months where each column is a month then create a measure for the TRIMMEAN? If so, how do I create this table? The issue is that I want to exclude outlying months and I can't find an example that sums the sales by date and then calculates the trimmed mean. I am also very new to this so may be misunderstanding what I've seen. If there is a better way please let me know. Thanks.
Solved
Revolution
2 years ago Place DAX Commands and Tips
1.3KViews
2likes
5Comments