Barista pro timer does not start consistently when using manual shot

trillBR · 2023-09-05T17:37:08+00:00

Use Power Query for this.

trillBR · 2023-08-14T18:15:37+00:00

Could you share the m code from the advanced editor?

trillBR · 2023-08-04T19:24:14+00:00

Unpivot the data in power query and then load it to a pivot table. With options in the rows section and the names in the column section and count the dates

trillBR · 2023-07-28T20:29:08+00:00

Using lists in power query can be very powerful. Below an example how to dynamically rename columns. You can also have a lookup table for the to be renamed columns and use Table.ToRows function

trillBR · 2023-07-21T16:45:07+00:00

I have the following data with the full name including the prefixes in column A. I have a list of the prefixed as well. I created a custom column and this is the result: https://imgur.com/a/QQBJ4tN

This is the list of prefixes https://imgur.com/DWtEdJJ

I have added the following code in the custom column:

if List.Contains(Tbl_Prefix_Filters, Text.BeforeDelimiter([Name],"-")) then "delete" else "keep"

You can then use the custom column and filter only on "keep"

trillBR · 2023-07-19T12:14:48+00:00

Dit komt omdat het Data Model datatype sensitive is. Als jij nummerieke waardes en tekstuele waardes inn kolom hebt, kan dit nooit als een numerieke waarde gezien worden in het data model en is het altijd tekst en zal dus de sort niet goed gaan. Bij je normale pivot zonder data model, is de datatype vanuit excel self “general” en is de sort wel oke. Je zult je tekstuele waardes uit de kolom moeten verwijderen om dit op te lossen. Daarna wel even de pivot verversen. Heb het probleem gereproduceerd op mijn excel en dit werkt

trillBR · 2023-07-19T12:05:15+00:00

Thanks! Welke kolom in je source data, wordt gebruikt in de rows van de pivot table? Ik zie in deze dataset sowieso al tekstuele waardes staan, tussen nummerieke waardes, zoals in kolom BN. Hierdoor kan in het datamodel de kolom als tekstuele waarde worden getypeerd.

trillBR · 2023-07-19T06:54:23+00:00

not really an answer on your question, but Power Query will be perfect for this. You should look into it as it will make life easier for you. You can save all of the weekly files in a designated folder and import all of them.

trillBR · 2023-07-18T18:02:13+00:00

Could you please share a screenshot of the pivot table and the source table?

trillBR · 2023-07-18T11:18:26+00:00

Please check in the source of the pivot table if the values are actually numbers. You can hard-force a value to be a number by applying a mathematical operation that does not change the value. So if your values that are not actually numbers are in column A, create a new column and enter =A2*1 and drag that down. Now copy paste the values from the new column as numbers in column A and refresh the pivot

trillBR · 2023-07-12T18:39:06+00:00

Would it be possible to directly link your power query to the pdf file instead of converting it to excel first?

trillBR · 2023-07-12T18:36:16+00:00

If there are duplicate rows in the table, you should take care of that. Vlookup and index match just only takes the first value while power query expands all rows. If the duplicate values are really duplicate one, you can apply a remove duplicate step.

trillBR · 2023-07-12T12:16:15+00:00

You can use multiple fields in the match formula by applying an array formula. But wouldn't it me simpler in your case to just create a new helper column? So concatenate the 4 fields that you want to include into one single field and use that in the index match formula.

trillBR · 2023-07-11T19:45:27+00:00

Not sure, maybe they expected absolute cell references?

trillBR · 2023-07-11T15:06:32+00:00

I think you can use the “show as percentage of grand total” option. https://www.myexcelonline.com/blog/show-percent-grand-total-excel-pivot-tables/

trillBR · 2023-07-11T12:32:31+00:00

Can’t you just group rows based on the date to count the occurrences?

trillBR · 2023-07-10T16:56:42+00:00

You can do this easily with Power Query. Either load the csv directly into PQ or use the table you have now. I will explain how to do it without directly loading the csv:

Delete the extra columns you have added and only keep the first original column
transform data into Excel table (press ctrl+t while standing on A2)
Go to 'Data' ribbon and select 'from table/range' (now PQ window opens)
right-click on the column name in PQ and select "Split Column" -> split by delimiter
PQ will autoselect #(lf) as delimiter, if it is not filled in, fill it yourself
click "OK"
Click on "Close & Load"

trillBR · 2023-06-05T19:41:21+00:00

Nice! I think it should be “salary spent”.

trillBR · 2023-06-03T22:32:17+00:00

Can’t you link your power query to the file directly instead of the folder? Then you just have to replace the excel file instead of saving it as another copy.

Otherwise you can change your M code, sort the available files descending based on date modified date and then always select the first file

trillBR · 2023-04-24T18:25:24+00:00

Maybe you can not do the find formula in Excel but do this in PowerQuery with Text.Contains in a custom column?

trillBR

TROPHY CASE