cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
phchu1226
Helper III
Helper III

Error on MIN function while creating custom column

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.

phchu1226_0-1632368101990.png

phchu1226_1-1632368111689.png

 

2 ACCEPTED SOLUTIONS
VahidDM
Resident Rockstar
Resident Rockstar

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🙏!!

View solution in original post

VahidDM
Resident Rockstar
Resident Rockstar

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 ✌️!!

 

View solution in original post

16 REPLIES 16
VahidDM
Resident Rockstar
Resident Rockstar

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✌️!!

 

Hi @VahidDM ,

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

phchu1226_2-1632369787604.png

 

phchu1226_1-1632369752422.png

 

VahidDM
Resident Rockstar
Resident Rockstar

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✌️!!

Hi, @VahidDM,

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

phchu1226_0-1632375064424.png

what means of this part?

phchu1226_1-1632375144037.png

 

 

 

VahidDM
Resident Rockstar
Resident Rockstar

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"

 

VahidDM_0-1632375570136.png

 

 

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

Appreciate your Kudos✌️!!

 

Hi @VahidDM,

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

phchu1226_0-1632377395009.png

 

 

VahidDM
Resident Rockstar
Resident Rockstar

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🙏!!

VahidDM
Resident Rockstar
Resident Rockstar

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!!

VahidDM
Resident Rockstar
Resident Rockstar

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🙏!!

View solution in original post

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
Resident Rockstar
Resident Rockstar

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 ✌️!!

 

View solution in original post

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.

phchu1226_2-1632452679776.png

phchu1226_3-1632452716313.png

 

VahidDM
Resident Rockstar
Resident Rockstar

@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 🙏!!

 

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          

phchu1226_0-1632463241368.png

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!