notebooks/PowerFx.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Install .NET and Tools\n",
    "\n",
    "1. [.NET Framework](https://dot.net/learntocode)\n",
    "2. [Polyglot Notebooks Extension](https://marketplace.visualstudio.com/items?itemName=ms-dotnettools.dotnet-interactive-vscode)\n",
    "3. [Data Table Extension](https://marketplace.visualstudio.com/items?itemName=RandomFractalsInc.vscode-data-table)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Useful videos to watch regarding Polyglot Notebooks\n",
    "\n",
    ".NET Interactive Notebooks was renamed to Polyglot Notebooks.\n",
    "\n",
    "1. [Learn C# with Interactive Notebooks](https://www.youtube.com/watch?v=xdmdR2JfKfM)\n",
    "2. [NET Interactive Notebooks with C#/F# in VS Code](https://www.youtube.com/watch?v=DMYtIJT1OeU)\n",
    "3. [.NET Everywhere - Windows, Linux, and Beyond](https://www.youtube.com/watch?v=ZM6OO2lkxA4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Install Power Fx - Local Build"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    },
    "polyglot_notebook": {
     "kernelName": "csharp"
    }
   },
   "outputs": [],
   "source": [
    "#i nuget:D:\\GitHub\\Power-Fx-.NET-Interactive\\src\\bin\\Debug\\\n",
    "#r \"nuget:PowerFx.NET.Interactive\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Install Power Fx kernel from Nuget"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "outputs": [],
   "source": [
    "#r \"nuget: PowerFx.NET.Interactive\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Known Issues\n",
    "\n",
    "1. Intellisense does not work for multiple lines."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Set Variable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "powerfx"
    },
    "polyglot_notebook": {
     "kernelName": "powerfx"
    }
   },
   "outputs": [],
   "source": [
    "Set(environment, \"Polyglot Notebooks 💗 Power Fx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Set Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "powerfx"
    },
    "polyglot_notebook": {
     "kernelName": "powerfx"
    }
   },
   "outputs": [],
   "source": [
    "ClearCollect( Products,\n",
    "    Table(\n",
    "        { Product: \"Widget\",    'Quantity Requested': 6,  'Quantity Available': 3 },\n",
    "        { Product: \"Gadget\",    'Quantity Requested': 10, 'Quantity Available': 20 },\n",
    "        { Product: \"Gizmo\",     'Quantity Requested': 4,  'Quantity Available': 11 },\n",
    "        { Product: \"Apparatus\", 'Quantity Requested': 7,  'Quantity Available': 6 }\n",
    "    )\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can now evaluate the Products collection."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "powerfx"
    },
    "polyglot_notebook": {
     "kernelName": "powerfx"
    }
   },
   "outputs": [],
   "source": [
    "First(Products).'Quantity Available'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Run Multiple Formulas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "powerfx"
    },
    "polyglot_notebook": {
     "kernelName": "powerfx"
    }
   },
   "outputs": [],
   "source": [
    "Set(varInput, ForAll( [\"4\"], Sqrt( Value ) ));\n",
    "Sequence(4);\n",
    "First(varInput);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Filtering"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "powerfx"
    },
    "polyglot_notebook": {
     "kernelName": "powerfx"
    }
   },
   "outputs": [],
   "source": [
    "ClearCollect( Products,\n",
    "    Table(\n",
    "        { Product: \"Widget\",    'Quantity Requested': 6,  'Quantity Available': 3 },\n",
    "        { Product: \"Gadget\",    'Quantity Requested': 10, 'Quantity Available': 20 },\n",
    "        { Product: \"Gizmo\",     'Quantity Requested': 4,  'Quantity Available': 11 },\n",
    "        { Product: \"Apparatus\", 'Quantity Requested': 7,  'Quantity Available': 6 }\n",
    "    )\n",
    ");\n",
    "First(Products);\n",
    "AddColumns( Filter( Products, 'Quantity Requested' > 'Quantity Available' ), 'Quantity To Order', 'Quantity Requested' - 'Quantity Available');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ParseJSON\n",
    "\n",
    "While _ParseJSON_ can be used in Power Fx, there is a known issue in this kernel to that prevents the output of _ParseJSON_ for being displayed properly because it is an _UntypedObjectValue_. The workaround for this issue is to use _ForAll_ and construct a strongly typed object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "powerfx"
    },
    "polyglot_notebook": {
     "kernelName": "powerfx"
    }
   },
   "outputs": [],
   "source": [
    "ForAll(Table(ParseJSON( \"[{ \"\"name\"\": \"\"Iron Man\"\" }, { \"\"name\"\": \"\"Dark Knight\"\" }]\")), { name: Text(ThisRecord.Value.name) } )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "powerfx"
    },
    "polyglot_notebook": {
     "kernelName": "powerfx"
    }
   },
   "outputs": [],
   "source": [
    "Table(ParseJSON( \"[{ \"\"name\"\": \"\"Iron Man\"\" }, { \"\"name\"\": \"\"Dark Knight\"\" }]\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Querying Dataverse\n",
    "\n",
    "You can query a single Dataverse environment by using the _#!dataverse-powerfx_ magic command with the connection string parameter."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "powerfx"
    },
    "polyglot_notebook": {
     "kernelName": "powerfx"
    }
   },
   "outputs": [],
   "source": [
    "#!dataverse-powerfx -c \"AuthType=OAuth; Url=https://dreamingincrmdev.crm6.dynamics.com; AppId=51f81489-12ee-4a9e-aaae-a2591f45987d; RedirectUri=http://localhost; TokenCacheStorePath=./tokencache; LoginPrompt=Auto\"\n",
    "ForAll(FirstN(Users,1), {name: 'Full Name', email: 'Primary Email', id: 'User' })"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you are already logged into Azure using Azure CLI, VSCode, Visual Studio or Azure PowerShell you can also get a token via Azure and use that to connect to Dataverse. If you have non of these installed locally browser popup will be used as the fall back mechanism to get the token via interactive login. This is a bit safe because you don't need to put ClientId/Secret in plain text."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "powerfx"
    },
    "polyglot_notebook": {
     "kernelName": "powerfx"
    }
   },
   "outputs": [],
   "source": [
    "#!dataverse-powerfx -c \"AuthType=OAuth; Url=https://dreamingincrmdev.crm6.dynamics.com; AppId=51f81489-12ee-4a9e-aaae-a2591f45987d; RedirectUri=http://localhost; TokenCacheStorePath=./tokencache; LoginPrompt=Auto\"\n",
    "ClearCollect(Top2Solutions, ForAll(Filter(Solutions, 'Package Type' = 'Package Type (Solutions)'.Unmanaged), {name: 'Display Name', installedDate: 'Installed On', publisher: ThisRecord.Publisher.Name}));"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If an expression does not return any result, empty result will be returned."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "powerfx"
    },
    "polyglot_notebook": {
     "kernelName": "powerfx"
    }
   },
   "outputs": [],
   "source": [
    "#!dataverse-powerfx -e \"https://dreamingincrmdev.crm6.dynamics.com\"\n",
    "Filter('Flow Runs', 'Start time'>DateAdd(Now(),-1,TimeUnit.Hours))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also use _Set_ or _ClearCollect_ functions on data returned by Dataverse Power Fx statements."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "powerfx"
    },
    "polyglot_notebook": {
     "kernelName": "powerfx"
    }
   },
   "outputs": [],
   "source": [
    "#!dataverse-powerfx -e \"https://dreamingincrmdev.crm6.dynamics.com\"\n",
    "Set(varUnmanagedSolutions, Filter(Solutions, 'Package Type' = 'Package Type (Solutions)'.Unmanaged))\n",
    "ForAll(Table(ParseJSON( \"[{ \"\"name\"\": \"\"Iron Man\"\" }, { \"\"name\"\": \"\"Dark Knight\"\" }]\")), { name: Text(ThisRecord.Value.name) } )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you just want to query and project the results, you can do so like below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "powerfx"
    },
    "polyglot_notebook": {
     "kernelName": "powerfx"
    }
   },
   "outputs": [],
   "source": [
    "#!dataverse-powerfx -e \"https://dreamingincrmdev.crm6.dynamics.com\"\n",
    "ForAll(Filter(Solutions, 'Package Type' = 'Package Type (Solutions)'.Unmanaged), {name: 'Display Name', installedDate: 'Installed On', publisher: ThisRecord.Publisher.Name})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You cannot change the data in Dataverse though. The below statement *does not* work in Power Fx 1.3.0. It errors out with _'Defaults' is a recognized but not supported function._"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "powerfx"
    },
    "polyglot_notebook": {
     "kernelName": "powerfx"
    }
   },
   "outputs": [],
   "source": [
    "#!dataverse-powerfx -c \"AuthType=OAuth; Url=https://dreamingincrmdev.crm6.dynamics.com; AppId=51f81489-12ee-4a9e-aaae-a2591f45987d; RedirectUri=http://localhost; TokenCacheStorePath=.\\tokencache; LoginPrompt=Auto\"\n",
    "Patch(Accounts, Defaults(Accounts), {'Account Name': \"Hello\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "powerfx"
    },
    "polyglot_notebook": {
     "kernelName": "powerfx"
    }
   },
   "outputs": [],
   "source": [
    "#!dataverse-powerfx -e \"https://dreamingincrmdev.crm6.dynamics.com\"\n",
    "Patch(Accounts, First(Filter(Accounts, 'Account Name'=\"Sample1\")).AsV, { 'Main Phone':\"12345\"});\n",
    "ForAll(Accounts,{name:'Account Name', id: 'Account', phone: 'Main Phone'});\n",
    "As\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create variables in C#"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    }
   },
   "outputs": [],
   "source": [
    "using System.Collections.Generic;\n",
    "\n",
    "record Point(decimal x, decimal y);\n",
    "\n",
    "List<Point> points = [new Point(1m, 2m), new Point(1.1m, 2.2m)];\n",
    "var today = DateTime.Now;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Use variables from C# in Power Fx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "powerfx"
    },
    "polyglot_notebook": {
     "kernelName": "powerfx"
    }
   },
   "outputs": [],
   "source": [
    "#!share points --from csharp\n",
    "#!share today --from csharp\n",
    "Set(varePoints, points);\n",
    "Set(varToday, today);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Set variables in Power Fx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "powerfx"
    },
    "polyglot_notebook": {
     "kernelName": "powerfx"
    }
   },
   "outputs": [],
   "source": [
    "ClearCollect( Catalog,\n",
    "    Table(\n",
    "        { Product: \"Widget\",    'QuantityRequested': 6,  'QuantityAvailable': 3 },\n",
    "        { Product: \"Gadget\",    'QuantityRequested': 10, 'QuantityAvailable': 20 }\n",
    "    )\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Use variable from Power Fx in JavaScript"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "javascript"
    },
    "polyglot_notebook": {
     "kernelName": "javascript"
    }
   },
   "outputs": [],
   "source": [
    "#!set --value @powerfx:Catalog --name catalog\n",
    "console.log(catalog.filter(x=>x.QuantityRequested >= 10));"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using C# to create new Functions in Power Fx \n",
    "You can create new Functions by extending a new class from ReflectionFunction. The classname should end with Function. Let us implement a sample Functions:\n",
    "\n",
    "Slice - This is similar to JavaScript Array.slice function. It accepts 3 parameters: The Table to slice records from, starting index of the slice, and number of items to slice. You can pass Blank, if you want all the items from the starting index of slice."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    },
    "polyglot_notebook": {
     "kernelName": "csharp"
    }
   },
   "outputs": [],
   "source": [
    "using Microsoft.PowerFx;\n",
    "using Microsoft.PowerFx.Types;\n",
    "using PowerFxDotnetInteractive;\n",
    "\n",
    "private class SliceFunction : ReflectionFunction\n",
    "{\n",
    "\tpublic SliceFunction()\n",
    "\t\t: base(\"Slice\", TableType.Empty().Add(\"Name\", FormulaType.String).Add(\"Rating\", FormulaType.Number),TableType.Empty().Add(\"Name\", FormulaType.String).Add(\"Rating\", FormulaType.Number), FormulaType.Number, FormulaType.Number)\n",
    "\t{\n",
    "\t}\n",
    "\n",
    "\tpublic TableValue Execute(TableValue table, NumberValue skipCount, NumberValue takeCount)\n",
    "\t{\n",
    "\t\treturn TableValue.NewTable(table.Rows.First().Value.Type, table.Rows.Skip((int)skipCount.Value).Take((int)takeCount.Value).Select(x => x.Value).ToArray());\n",
    "\t}\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let us add the function to the Power Fx Engine. We obtain the instance of RecalcEngine from the Power Fx kernel and add our custom Function to the config."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    },
    "polyglot_notebook": {
     "kernelName": "csharp"
    }
   },
   "outputs": [],
   "source": [
    "PowerFxKernel.GetRecalcEngine().Config.AddFunction(new SliceFunction())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can now check if the new Function has been added to the available Symbols as below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    },
    "polyglot_notebook": {
     "kernelName": "csharp"
    }
   },
   "outputs": [],
   "source": [
    "PowerFxKernel.GetRecalcEngine().Config.SymbolTable.FunctionNames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can use _Slice_ Function like any native Power Fx Function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "powerfx"
    },
    "polyglot_notebook": {
     "kernelName": "powerfx"
    }
   },
   "outputs": [],
   "source": [
    "Set(MovieTable, Table({ Name: \"Iron Man\", Rating: 10 }, { Name: \"Captain America\", Rating: 10 }, { Name: \"Dark Knight\", Rating: 10 }));\n",
    "Slice(MovieTable, 1, 1);\n",
    "Slice(MovieTable, 1, 2);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Adding Power Fx Custom Functions using Power Fx\n",
    "\n",
    "You can also define a Power Fx function similar to a short lambda Function. e.g. multiplying two numbers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    },
    "polyglot_notebook": {
     "kernelName": "csharp"
    }
   },
   "outputs": [],
   "source": [
    "using PowerFxDotnetInteractive;\n",
    "\n",
    "PowerFxKernel.GetRecalcEngine().AddUserDefinitions(\"Multiply(a:Number, b:Number):Number = a * b;\");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can now call the defined Function in Power Fx."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "dotnet_interactive": {
     "language": "powerfx"
    },
    "polyglot_notebook": {
     "kernelName": "powerfx"
    }
   },
   "outputs": [],
   "source": [
    "Multiply(10,2.4)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".NET (C#)",
   "language": "C#",
   "name": ".net-csharp"
  },
  "language_info": {
   "name": "polyglot-notebook"
  },
  "polyglot_notebook": {
   "kernelInfo": {
    "defaultKernelName": "csharp",
    "items": [
     {
      "aliases": [],
      "name": "csharp"
     },
     {
      "aliases": [],
      "languageName": "powerfx",
      "name": "powerfx"
     }
    ]
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}