Error on MIN function while creating custom column

[phchu1226]

Hi all,

I want to write a formula to extract the min. date from the column. However, the "MIN" function seems do not work..?

Do anyone know how to achieve this? Thank you.

[VahidDM · Accepted solution]

Hi @phchu1226 

I checked your file, use this code:

= Table.AddColumn(SOP_PROD_OS1, "Custom", each if [S_DATE]=List.Min(SOP_PROD_OS1[#"S_DATE"]) then "Week0" else "Week1")

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos🙏!!

[VahidDM]

Hi @phchu1226 

Try to use List.Min code rather than Min in your code.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos✌️!!

[phchu1226]

Hi @VahidDM ,

Tried List.Min, another error occur..no idea how to do this in power query..

[VahidDM]

Hi @phchu1226 

Try this code:

if [S_Date]=List.Min(#"Renamed Columns"[#"S_Date"]) then "Week0" else "Week1"

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos✌️!!

[phchu1226]

Hi, @VahidDM,

I tried to paste the code above to the "custom column formula" and the below error occur..

what means of this part?

[VahidDM]

Hi @phchu1226 

Change that #"Renamed Columns" in the code to the last step name, for instance if the last step is Changed Type, code should be :

if [S_Date]=List.Min(#"Changed Type"[#"S_Date"]) then "Week0" else "Week1"

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos✌️!!

[phchu1226]

Hi @VahidDM,

Just tried the code again, which part I did wrongly?

[VahidDM]

Hi @phchu1226 

Try this:

= Table.AddColumn(#"Navigation", "Custom", each if [S_Date]=List.Min(#"Navigation"[#"S_Date"]) then "Week0" else "Week1")

if it does not work, send me your file if it's possible (after removing sensetive data)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos🙏!!

[phchu1226]

Hi @VahidDM,

May I give you my dataset? Thank you

https://hunghing-my.sharepoint.com/:x:/g/personal/calvin_chu_hunghingprinting_com/EY0IkjNiAKpLpLzG-t...

[VahidDM]

This link does not work. Can you dump those data on an excel file or import them to power bi desktop, then share one of those files with me.

Appreciate your Kudos!!

[phchu1226]

Hi @VahidDM,

can you try this again?
https://hunghing-my.sharepoint.com/:u:/g/personal/calvin_chu_hunghingprinting_com/EU42fQcjK0RMitdW-S...

[VahidDM · Accepted solution]

Hi @phchu1226 

I checked your file, use this code:

= Table.AddColumn(SOP_PROD_OS1, "Custom", each if [S_DATE]=List.Min(SOP_PROD_OS1[#"S_DATE"]) then "Week0" else "Week1")

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos🙏!!

[phchu1226]

Hi @VahidDM,

Finally it works.. so I should not put step name (#"Navigation") here?

btw, is it possible to filter latest 7 date in query editor?

[VahidDM · Accepted solution]

The last step in your file is "SOP_PROD_OS1", you can find it if you open the advance editor.
It shows Navigation because your file is connected to Oracle DB.

You can find the Last Date in your list, then find the seven days before the date, and write an if statement to put yes if the date is => than that date.

At the end, filter on Yes on the new column.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos ✌️!!

[phchu1226]

Hi @VahidDM,

Excellent, it works well! Thank you so much.

Last question I want to ask is I dont know whether powerbi have any feature or trick can achieve this..

I want to append a column there which display the fixed reference number.

[VahidDM]

@phchu1226 

Can you please tell me more details, do you want to add a column with specific data for each item to the matrix?

Appreciate your Kudos 🙏!!

[phchu1226]

Hi @VahidDM,

yes, for example,

I want a column like the below screencap, i.e. 

STATUS  CAPACITY  WEEK0 WEEK1 ....

A             1500         544        211

B             3000         6451      591 

C             4500         30