Copy Paste Advance Editor Wouldn't Work / Custom Calculation Table Return Error

[Anonymous]

Does anyone has the same issue to replicate conditioning table on several Power BI files ?

I can replicate renamed collumn, rounded, changed type, but not conditional collumn or additonal collumn.

Below is my partial advance editior text : Any errors ?

#"Added Custom3" = Table.AddColumn(#"Rounded Off2", "Rev Volume US$", each [#"Volume US$"]*0.9),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom3",{{"Rev Volume US$", Currency.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type3", "Customer", each if [#"Channel / End User"] = "End User" then [#"Channel / End User"] else [End User]),
#"Added Custom4" = Table.AddColumn(#"Added Conditional Column", "Delivery-Close Date", each [Delivery Date]-[Expected Close Date]),
#"Added Conditional Column1" = Table.AddColumn(#"Added Custom4", "B&S", each if [#"Delivery-Close Date"] < #duration(90, 0, 0, 0) then "Yes" else if [#"Delivery-Close Date"] <= #duration(0, 0, 0, 0) then "Date Error" else if [#"Delivery-Close Date"] = null then "Date Error" else "No"),
#"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Op1", each if [#"FC%"] = "100%" then 1 else if [#"FC%"] = "80%" then 1 else if [#"FC%"] = "60%" then 1 else if [#"FC%"] = "40%" then 2 else 0),
#"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "Op2", each if [#"DP%"] = "100%" then 1 else if [#"DP%"] = "80%" then 1 else if [#"DP%"] = "60%" then 1 else 0),
#"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "Custom", each if [Op1] = [Op2] then "Commited" else if [Op2] = 0 then "Non Commited" else "Upside")
in
#"Added Conditional Column4"

Besides, I am trying to calculate number of days from : Delivery Date - Expected Date, because some of my date filed is not filled, hence some result returned null. Then when I performed IF Function with the conditional collumn: If Delivery Date - Expected Date = null, Power BI will return Error, that is when my Power BI will have refresh error. Shown in the print screen on left.

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

What's error messages? Did you set the proper connection? Sometimes you need to configure the connection and the privacy level.

Best Regards,

Dale

[v-jiascu-msft]

Hi @Anonymous,

It seems fine. What are the errors exactly? Can you share them with us? You can click the "View errors" for details. If we know what the errors are, we can troubleshoot the queries.

Best Regards,

Dale

[Anonymous]

Hi Jiascu,

The error has been fixed and is not interrupting my refresh schedule.
My concern now is with the advance editor script, which I tried copy and paste on a new Power Bi file but it won’t work. I have to manually redo all the steps again. Please help with this advance editor script.

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

What's error messages? Did you set the proper connection? Sometimes you need to configure the connection and the privacy level.

Best Regards,

Dale

[Anonymous]

Apparently it works when I restarted my computer and perhaps my connection was reset.

If I don't reset, how do I go about configuring the connection at privacy level ? Mind share a screenshot and steps ?

Thank You

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

I'm glad you can go on now. Most of the time, there will be a prompt. You can change the settings as the picture shows. The exact solutions depend on the errors. 

Best Regards,

Dale