{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# User Guide" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Here is some examples to use ppr" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "# Import the libary into your script\n", "import pypbireport as ppr" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "---" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Working with Power BI reports\n" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Sample Report: 3 pages and 15 visuals" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Define the path for your report\n", "pbix_path = '../../example/Sample Report.pbix'\n", "\n", "# Instantiate a PBIReport object\n", "report = ppr.PBIReport(pbix_path=pbix_path)\n", "\n", "report" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Parse **Power BI report layout JSON** into a **Python dictionary**." ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Layout JSON in a dictionary: \n", "Number of keys and its names: 6, ['id', 'theme', 'resourcePackages', 'sections', 'config', 'layoutOptimization']\n" ] } ], "source": [ "# After reading a PBIX file, the layout information is stored in a Python dictionary\n", "foo = report.layout_pbi_dict\n", "\n", "print(f'Layout JSON in a dictionary: {type(foo)}')\n", "\n", "print(f'Number of keys and its names: {len(foo)}, {list(foo.keys())}')" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "3\n", "Cover\n" ] } ], "source": [ "# For example, the key 'sections' holds the pages of the report\n", "# Get the number of pages\n", "print(len(foo.get('sections')))\n", "\n", "# Get the name of first page in report\n", "print(foo.get('sections')[0].get('displayName'))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Manipulating Visuals" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Visuals in the report represented as a list of Visual objects" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[Visual( id: ba11b203b4ce6a5c7490 | page: Cover | hor,ver: (110,56) | h,w: (56, 318) | type: textbox ),\n", " Visual( id: dfb0ef76dbaad8215b35 | page: Cover | hor,ver: (110,97) | h,w: (14, 357) | type: shape ),\n", " Visual( id: 86673bea6b52652fd0e8 | page: Cover | hor,ver: (549,126) | h,w: (246, 180) | type: image ),\n", " Visual( id: 1c510b92f9664aa06f71 | page: Sales Reseller | hor,ver: (308,184) | h,w: (336, 676) | type: lineStackedColumnComboChart ),\n", " Visual( id: 74dc7d85aa39bf8a5880 | page: Sales Reseller | hor,ver: (92,82) | h,w: (48, 249) | type: textbox ),\n", " Visual( id: 2ea41ed9ed62c60aab33 | page: Sales Reseller | hor,ver: (324,184) | h,w: (336, 614) | type: hundredPercentStackedColumnChart ),\n", " BookmarkSlicer( id: 70ac771ab9b71818c964 | page: Sales Reseller | hor,ver: (308,135) | h,w: (29, 100) | type: bookmarkNavigator ),\n", " Visual( id: 3656b63a1c5a1d5864fb | page: Sales Geography | hor,ver: (201,207) | h,w: (345, 414) | type: clusteredBarChart ),\n", " Visual( id: b48b6690e2c83b1a4339 | page: Sales Geography | hor,ver: (31,17) | h,w: (48, 339) | type: textbox ),\n", " Visual( id: a60fd9caebd151edd61b | page: Sales Geography | hor,ver: (201,207) | h,w: (358, 420) | type: clusteredBarChart ),\n", " BookmarkSlicer( id: 07788b861426e801f4c7 | page: Sales Geography | hor,ver: (201,160) | h,w: (36, 220) | type: bookmarkNavigator ),\n", " Visual( id: 2caadbd8b53008045d2d | page: Sales Geography | hor,ver: (711,106) | h,w: (201, 363) | type: funnel ),\n", " Slicer( id: a6a4486f8508b0878304 | page: Sales Geography | hor,ver: (31,63) | h,w: (144, 121) | type: slicer ),\n", " Column( id: 1aaebe320ae9e89ac467 | page: Sales Geography | hor,ver: (637,308) | h,w: (243, 571) | type: columnChart ),\n", " Card( id: 1a35ebc8b0a047328cc4 | page: Sales Geography | hor,ver: (201,66) | h,w: (61, 147) | type: card )]" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Visuals of the report\n", "report.visuals" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'Cover': [Visual( id: ba11b203b4ce6a5c7490 | page: Cover | hor,ver: (110,56) | h,w: (56, 318) | type: textbox ),\n", " Visual( id: dfb0ef76dbaad8215b35 | page: Cover | hor,ver: (110,97) | h,w: (14, 357) | type: shape ),\n", " Visual( id: 86673bea6b52652fd0e8 | page: Cover | hor,ver: (549,126) | h,w: (246, 180) | type: image )],\n", " 'Sales Reseller': [Visual( id: 1c510b92f9664aa06f71 | page: Sales Reseller | hor,ver: (308,184) | h,w: (336, 676) | type: lineStackedColumnComboChart ),\n", " Visual( id: 74dc7d85aa39bf8a5880 | page: Sales Reseller | hor,ver: (92,82) | h,w: (48, 249) | type: textbox ),\n", " Visual( id: 2ea41ed9ed62c60aab33 | page: Sales Reseller | hor,ver: (324,184) | h,w: (336, 614) | type: hundredPercentStackedColumnChart ),\n", " BookmarkSlicer( id: 70ac771ab9b71818c964 | page: Sales Reseller | hor,ver: (308,135) | h,w: (29, 100) | type: bookmarkNavigator )],\n", " 'Sales Geography': [Visual( id: 3656b63a1c5a1d5864fb | page: Sales Geography | hor,ver: (201,207) | h,w: (345, 414) | type: clusteredBarChart ),\n", " Visual( id: b48b6690e2c83b1a4339 | page: Sales Geography | hor,ver: (31,17) | h,w: (48, 339) | type: textbox ),\n", " Visual( id: a60fd9caebd151edd61b | page: Sales Geography | hor,ver: (201,207) | h,w: (358, 420) | type: clusteredBarChart ),\n", " BookmarkSlicer( id: 07788b861426e801f4c7 | page: Sales Geography | hor,ver: (201,160) | h,w: (36, 220) | type: bookmarkNavigator ),\n", " Visual( id: 2caadbd8b53008045d2d | page: Sales Geography | hor,ver: (711,106) | h,w: (201, 363) | type: funnel ),\n", " Slicer( id: a6a4486f8508b0878304 | page: Sales Geography | hor,ver: (31,63) | h,w: (144, 121) | type: slicer ),\n", " Column( id: 1aaebe320ae9e89ac467 | page: Sales Geography | hor,ver: (637,308) | h,w: (243, 571) | type: columnChart ),\n", " Card( id: 1a35ebc8b0a047328cc4 | page: Sales Geography | hor,ver: (201,66) | h,w: (61, 147) | type: card )]}" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# To facilitate page division, there is a dictionary containing a list of visuals for each page\n", "report.pages_visuals" ] }, { "cell_type": "code", "execution_count": 55, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
visualidtypeposition
pagename
Coverba11b203b4ce6a5c7490textbox{'x': 110, 'y': 56}
Coverdfb0ef76dbaad8215b35shape{'x': 110, 'y': 97}
Cover86673bea6b52652fd0e8image{'x': 549, 'y': 126}
\n", "
" ], "text/plain": [ " visualid type position\n", "pagename \n", "Cover ba11b203b4ce6a5c7490 textbox {'x': 110, 'y': 56}\n", "Cover dfb0ef76dbaad8215b35 shape {'x': 110, 'y': 97}\n", "Cover 86673bea6b52652fd0e8 image {'x': 549, 'y': 126}" ] }, "execution_count": 55, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# You can visualize a summary of visuals as a Pandas DataFrame\n", "report.resume_report_visuals()[['visualid', 'type', 'position', 'size']].head(3).iloc[:,:3]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Modifying Visuals properties" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Type of visual: clusteredBarChart\n", "Vertical position: 207.2164948453608\n", "\n", "Change vertical position\n", "New vertical position: 150\n" ] } ], "source": [ "# Visual objects have attributes that can be modified\n", "\n", "# An example\n", "visual = report.visuals[9]\n", "print(f'Type of visual: {visual.visual_type}')\n", "# Get information from visual\n", "print(f'Vertical position: {visual.vertical}')\n", "# Modify the vertical positon of visual and check\n", "print(f'\\nChange vertical position')\n", "visual.vertical = 150\n", "print(f'New vertical position: {visual.vertical}')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Modifying Visuals properties through the Visual dictionary" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'x': 31.546391752577318,\n", " 'y': 17.31958762886598,\n", " 'z': 1000,\n", " 'width': 339.58762886597935,\n", " 'height': 48.865979381443296,\n", " 'config': {'name': 'b48b6690e2c83b1a4339',\n", " 'layouts': [{'id': 0,\n", " 'position': {'x': 31.546391752577318,\n", " 'y': 17.31958762886598,\n", " 'z': 1000,\n", " 'width': 339.58762886597935,\n", " 'height': 48.865979381443296,\n", " 'tabOrder': 1000}}],\n", " 'singleVisual': {'visualType': 'textbox',\n", " 'drillFilterOtherVisuals': True,\n", " 'objects': {'general': [{'properties': {'paragraphs': [{'textRuns': [{'value': 'Reseller Sales Geography Analysis',\n", " 'textStyle': {'fontWeight': 'bold', 'fontSize': '16pt'}}]}]}}]}}},\n", " 'filters': [],\n", " 'tabOrder': 1000,\n", " 'query': {},\n", " 'dataTransforms': {}}" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# The 'visual' attribute of Visual objects contains the dictionary that is \n", "# rendered in Power BI. So, all modifications can be made directly in it.\n", "\n", "visual = report.visuals['b48b6690e2c83b1a4339'] #use id for slicing\n", "visual.visual" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Value of font size: 16pt\n", "New value: 20pt\n" ] } ], "source": [ "# For example, let's change the font size of a text box\n", "\n", "# Get the value from the visual dictionary and set a new value for it\n", "font_size = ( \n", " visual.visual['config']['singleVisual']['objects']['general'][0]\n", " ['properties']['paragraphs'][0]['textRuns'][0]['textStyle']['fontSize'] \n", ")\n", "print(f'Value of font size: {font_size}')\n", "\n", "# Set a new value through the dictionary \n", "( \n", " visual.visual['config']['singleVisual']['objects']['general'][0]\n", " ['properties']['paragraphs'][0]['textRuns'][0]['textStyle']['fontSize'] \n", ") = '20pt' \n", "\n", "# Confirming the new value\n", "font_size = (\n", " visual.visual['config']['singleVisual']['objects']['general'][0]\n", " ['properties']['paragraphs'][0]['textRuns'][0]['textStyle']['fontSize']\n", ")\n", "print(f'New value: {font_size}')" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [], "source": [ "# The above modification can also be achieved using the 'update' method\n", "(\n", " visual.visual['config']['singleVisual']['objects']['general'][0]\n", " ['properties']['paragraphs'][0]['textRuns'][0]['textStyle']\n", " .update({'fontSize':'20pt'})\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Adding a Visual to the report" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "For adding a new visual to the report, we have two options: either retrieve an existing one or create a new one from a template" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Retrieve an existing visual and create a copy" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[Visual( id: 3656b63a1c5a1d5864fb | page: Sales Geography | hor,ver: (201,207) | h,w: (345, 414) | type: clusteredBarChart ),\n", " Visual( id: b48b6690e2c83b1a4339 | page: Sales Geography | hor,ver: (31,17) | h,w: (48, 339) | type: textbox ),\n", " Visual( id: a60fd9caebd151edd61b | page: Sales Geography | hor,ver: (201,150) | h,w: (358, 420) | type: clusteredBarChart ),\n", " BookmarkSlicer( id: 07788b861426e801f4c7 | page: Sales Geography | hor,ver: (201,160) | h,w: (36, 220) | type: bookmarkNavigator ),\n", " Visual( id: 2caadbd8b53008045d2d | page: Sales Geography | hor,ver: (711,106) | h,w: (201, 363) | type: funnel ),\n", " Slicer( id: a6a4486f8508b0878304 | page: Sales Geography | hor,ver: (31,63) | h,w: (144, 121) | type: slicer ),\n", " Column( id: 1aaebe320ae9e89ac467 | page: Sales Geography | hor,ver: (637,308) | h,w: (243, 571) | type: columnChart ),\n", " Card( id: 1a35ebc8b0a047328cc4 | page: Sales Geography | hor,ver: (201,66) | h,w: (61, 147) | type: card )]" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# First, get an existing visual. Let's work with one specific page this time\n", "\n", "sg_visuals = report.pages_visuals['Sales Geography'] #using page_visuals\n", "sg_visuals" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The hexadecimal id: 1aaebe320ae9e89ac467\n", "The height: 243.71134020618555\n", "The measure in y field: Metrics.Sales\n", "The horizontal position: 637.1134020618557\n" ] } ], "source": [ "# Get a column chart from the page\n", "visual_column = sg_visuals['1aaebe320ae9e89ac467']\n", "\n", "# See some properties\n", "print(f'The hexadecimal id: {visual_column.id}')\n", "print(f'The height: {visual_column.height}')\n", "print(f'The measure in y field: {visual_column.y}')\n", "print(f'The horizontal position: {visual_column.horizontal}')\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "To create a copy of visual, use the function `copy_visual`" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Column( id: 906fd8155f34f3c14afb | page: | hor,ver: (637,308) | h,w: (243, 571) | type: columnChart )" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "visual_column_copy = ppr.copy_visual(visual_column)\n", "\n", "visual_column_copy" ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The hexadecimal id: 906fd8155f34f3c14afb\n", "The height: 200\n", "The measure in y field: Metrics.Sales\n", "The horizontal position: 200\n" ] } ], "source": [ "# Modfify some properties\n", "visual_column_copy.height = 200\n", "visual_column_copy.horizontal = 200\n", "visual_column_copy.vertical = 100\n", "\n", "print(f'The hexadecimal id: {visual_column_copy.id}') #this will be different\n", "print(f'The height: {visual_column_copy.height}')\n", "print(f'The measure in y field: {visual_column_copy.y}')\n", "print(f'The horizontal position: {visual_column_copy.horizontal}')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The copied visual doesn't have an allocated page \n", "You should now use the report's `insert_visual_in_page(page_name, visual)` method to add the visual." ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [], "source": [ "# Using the function `insert_visual_in_page` to add the visual\n", "report.insert_visual_in_page(page_name='Sales Geography', visual=visual_column_copy)" ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[Visual( id: 3656b63a1c5a1d5864fb | page: Sales Geography | hor,ver: (201,207) | h,w: (345, 414) | type: clusteredBarChart ),\n", " Visual( id: b48b6690e2c83b1a4339 | page: Sales Geography | hor,ver: (31,17) | h,w: (48, 339) | type: textbox ),\n", " Visual( id: a60fd9caebd151edd61b | page: Sales Geography | hor,ver: (201,150) | h,w: (358, 420) | type: clusteredBarChart ),\n", " BookmarkSlicer( id: 07788b861426e801f4c7 | page: Sales Geography | hor,ver: (201,160) | h,w: (36, 220) | type: bookmarkNavigator ),\n", " Visual( id: 2caadbd8b53008045d2d | page: Sales Geography | hor,ver: (711,106) | h,w: (201, 363) | type: funnel ),\n", " Slicer( id: a6a4486f8508b0878304 | page: Sales Geography | hor,ver: (31,63) | h,w: (144, 121) | type: slicer ),\n", " Column( id: 1aaebe320ae9e89ac467 | page: Sales Geography | hor,ver: (637,308) | h,w: (243, 571) | type: columnChart ),\n", " Card( id: 1a35ebc8b0a047328cc4 | page: Sales Geography | hor,ver: (201,66) | h,w: (61, 147) | type: card ),\n", " Column( id: 906fd8155f34f3c14afb | page: Sales Geography | hor,ver: (200,100) | h,w: (200, 571) | type: columnChart )]" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Checking visual in page\n", "report.pages_visuals['Sales Geography']" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The last one is the copy of column chart" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Create a new visual from a template" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Use the fucntion `create_new_visual(visual: ['card', 'column', 'slicer_drop', 'slicer_list', 'bookmark_slicer'], page_name, page_id)` \n", "\n", "The ppr provides five visual templates ('card', 'column', 'slicer_drop', 'slicer_list', 'bookmark_slicer'). \n", "There are simple preset visual dictionaries placed inside the package to ease visual creation. \n", "Additionally, copying a created visual may be more suitable." ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{0: ['Cover', 'ReportSection'],\n", " 1: ['Sales Reseller', 'ReportSectione5a5d9f92de0fef7e45e'],\n", " 2: ['Sales Geography', 'ReportSectionbe016ac3584b5b3bef65']}" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Different from the previous copy, where a copy is made without a page, for a new visual\n", "# you should determine its location.\n", "# To get the page_id, use the report function resume_report_pages()\n", "report.resume_report_pages()" ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Card( id: 47c4cb7acaf3f4f61c2e | page: Sales Geography | hor,ver: (164,233) | h,w: (68, 172) | type: card )" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Let's crate a card visual in Sales Geography\n", "card = ppr.create_new_visual(visual='card', \n", " page_name='Sales Geography', \n", " page_id='ReportSectionbe016ac3584b5b3bef65')\n", "\n", "card" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Important: the fact that a created visual has an assigned page doesn't mean that it is inserted into the report." ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [], "source": [ "# Again, you are free to modify and costumize the visual before insertion \n", "# into the report.\n", "card.title_text = \"PPR Card created\"\n", "card.height = 10\n", "card.horizontal = 100\n", "card.field = 'Metrics.Sales'\n", "\n", "report.insert_visual_in_page(page_name='Sales Geography', visual=card)" ] }, { "cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[Visual( id: 3656b63a1c5a1d5864fb | page: Sales Geography | hor,ver: (201,207) | h,w: (345, 414) | type: clusteredBarChart ),\n", " Visual( id: b48b6690e2c83b1a4339 | page: Sales Geography | hor,ver: (31,17) | h,w: (48, 339) | type: textbox ),\n", " Visual( id: a60fd9caebd151edd61b | page: Sales Geography | hor,ver: (201,150) | h,w: (358, 420) | type: clusteredBarChart ),\n", " BookmarkSlicer( id: 07788b861426e801f4c7 | page: Sales Geography | hor,ver: (201,160) | h,w: (36, 220) | type: bookmarkNavigator ),\n", " Visual( id: 2caadbd8b53008045d2d | page: Sales Geography | hor,ver: (711,106) | h,w: (201, 363) | type: funnel ),\n", " Slicer( id: a6a4486f8508b0878304 | page: Sales Geography | hor,ver: (31,63) | h,w: (144, 121) | type: slicer ),\n", " Column( id: 1aaebe320ae9e89ac467 | page: Sales Geography | hor,ver: (637,308) | h,w: (243, 571) | type: columnChart ),\n", " Card( id: 1a35ebc8b0a047328cc4 | page: Sales Geography | hor,ver: (201,66) | h,w: (61, 147) | type: card ),\n", " Column( id: 906fd8155f34f3c14afb | page: Sales Geography | hor,ver: (200,100) | h,w: (200, 571) | type: columnChart ),\n", " Card( id: 47c4cb7acaf3f4f61c2e | page: Sales Geography | hor,ver: (100,233) | h,w: (10, 172) | type: card )]" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Check if visual was added\n", "report.pages_visuals['Sales Geography']" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Creating Bookmarks" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Bookmark information" ] }, { "cell_type": "code", "execution_count": 22, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[Bookmark( id: 'Bookmark0ed6bff31aa9b8bd1871' | name: City | page: Sales Geography | show: ['3656b63a1c5a1d5864fb', 'b48b6690e2c83b1a4339', '07788b861426e801f4c7'] | hide: ['a60fd9caebd151edd61b'] ),\n", " Bookmark( id: 'Bookmark84f5abdb65c08d899418' | name: Country | page: Sales Geography | show: ['b48b6690e2c83b1a4339', 'a60fd9caebd151edd61b', '07788b861426e801f4c7'] | hide: ['3656b63a1c5a1d5864fb'] ),\n", " Bookmark( id: 'Bookmark5fa4a86292cd8509f1a7' | name: V | page: Sales Reseller | show: ['1c510b92f9664aa06f71', '74dc7d85aa39bf8a5880'] | hide: ['2ea41ed9ed62c60aab33'] ),\n", " Bookmark( id: 'Bookmark549dfb51b281266458c9' | name: P | page: Sales Reseller | show: ['74dc7d85aa39bf8a5880', '2ea41ed9ed62c60aab33'] | hide: ['1c510b92f9664aa06f71'] ),\n", " Bookmark( id: 'Bookmarkbeef068b5ca61ec2a331' | name: Botao Solto | page: Sales Geography | show: ['07788b861426e801f4c7'] | hide: ['3656b63a1c5a1d5864fb', 'b48b6690e2c83b1a4339', 'a60fd9caebd151edd61b'] )]" ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Resume bookmarks in the report\n", "report.bookmarks" ] }, { "cell_type": "code", "execution_count": 23, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[BookmarkGroup( id: 'Bookmark3a33e033c4557e665f3b' | name: City or Country | page: Sales Geography | bookmarks_id: ['Bookmark0ed6bff31aa9b8bd1871', 'Bookmark84f5abdb65c08d899418'] | bookmarks_name: ['City', 'Country'] | visuals: ['3656b63a1c5a1d5864fb', 'b48b6690e2c83b1a4339', '07788b861426e801f4c7', 'a60fd9caebd151edd61b', 'b48b6690e2c83b1a4339', 'a60fd9caebd151edd61b', '07788b861426e801f4c7', '3656b63a1c5a1d5864fb'] ),\n", " BookmarkGroup( id: 'Bookmark788448a1069d01cbd063' | name: V, P | page: Sales Reseller | bookmarks_id: ['Bookmark5fa4a86292cd8509f1a7', 'Bookmark549dfb51b281266458c9'] | bookmarks_name: ['V', 'P'] | visuals: ['1c510b92f9664aa06f71', '74dc7d85aa39bf8a5880', '2ea41ed9ed62c60aab33', '74dc7d85aa39bf8a5880', '2ea41ed9ed62c60aab33', '1c510b92f9664aa06f71'] )]" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Resume bookmarks groups in the report\n", "report.bookmark_groups" ] }, { "cell_type": "code", "execution_count": 24, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The bookmark id: Bookmark0ed6bff31aa9b8bd1871\n", "Name: City\n", "The to show visuals list: [Visual( id: 3656b63a1c5a1d5864fb | page: Sales Geography | hor,ver: (201,207) | h,w: (345, 414) | type: clusteredBarChart ), Visual( id: b48b6690e2c83b1a4339 | page: Sales Geography | hor,ver: (31,17) | h,w: (48, 339) | type: textbox ), BookmarkSlicer( id: 07788b861426e801f4c7 | page: Sales Geography | hor,ver: (201,160) | h,w: (36, 220) | type: bookmarkNavigator )]\n", "The hide one: [Visual( id: a60fd9caebd151edd61b | page: Sales Geography | hor,ver: (201,150) | h,w: (358, 420) | type: clusteredBarChart )]\n" ] } ], "source": [ "# The bookmarks don't have a specific page.\n", "# Get a bookmark\n", "book = report.bookmarks[0]\n", "\n", "print(f'The bookmark id: {book.id}')\n", "print(f'Name: {book.name}')\n", "print(f'The to show visuals list: {book.show_visuals}')\n", "print(f'The hide one: {book.hide_visuals}')" ] }, { "cell_type": "code", "execution_count": 25, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The bookmark id: Bookmark3a33e033c4557e665f3b\n", "The name: City or Country\n", "The bookmarks: [Bookmark( id: 'Bookmark0ed6bff31aa9b8bd1871' | name: City | page: Sales Geography | show: ['3656b63a1c5a1d5864fb', 'b48b6690e2c83b1a4339', '07788b861426e801f4c7'] | hide: ['a60fd9caebd151edd61b'] ), Bookmark( id: 'Bookmark84f5abdb65c08d899418' | name: Country | page: Sales Geography | show: ['b48b6690e2c83b1a4339', 'a60fd9caebd151edd61b', '07788b861426e801f4c7'] | hide: ['3656b63a1c5a1d5864fb'] )]\n" ] } ], "source": [ "# Bookmark groups are collection of bookmarks\n", "book_group = report.bookmark_groups[0]\n", "\n", "print(f'The bookmark id: {book_group.id}')\n", "print(f'The name: {book_group.bookmark_group_name}')\n", "print(f'The bookmarks: {book_group.children_list}')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Important to note**: the development for bookmarks was focused in \"buttons\" to show and hide visuals in the report. \n", "\n", "To be more specific, the intention is to work with preconfigured bookmarks of the following kind: \n", "\"image\" \n", "It is not guaranteed that the package works fine with other bookmarks. \n", "\n", "This is because there are attributes for show_visuals and hide_visuals. Nothing was designed for \"show page,\" for example." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Create a bookmark using Bookmark object" ] }, { "cell_type": "code", "execution_count": 26, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "List of visual to show: [Visual( id: 3656b63a1c5a1d5864fb | page: Sales Geography | hor,ver: (201,207) | h,w: (345, 414) | type: clusteredBarChart ), Visual( id: 2caadbd8b53008045d2d | page: Sales Geography | hor,ver: (711,106) | h,w: (201, 363) | type: funnel )]\n", "List of visual to hide: [Visual( id: a60fd9caebd151edd61b | page: Sales Geography | hor,ver: (201,150) | h,w: (358, 420) | type: clusteredBarChart ), Column( id: 1aaebe320ae9e89ac467 | page: Sales Geography | hor,ver: (637,308) | h,w: (243, 571) | type: columnChart )]\n" ] } ], "source": [ "# Remember that the bookmark is for \"buttons,\" so, first, we need visuals to show\n", "# and to hide\n", "\n", "to_show = (\n", " report.pages_visuals['Sales Geography']\n", " [['3656b63a1c5a1d5864fb', '2caadbd8b53008045d2d']] #pass a list of id of visuals\n", ")\n", "\n", "to_hide = (\n", " report.pages_visuals['Sales Geography']\n", " [['a60fd9caebd151edd61b', '1aaebe320ae9e89ac467']] #pass a list of id of visuals\n", ")\n", "\n", "print(f'List of visual to show: {to_show}')\n", "print(f'List of visual to hide: {to_hide}')" ] }, { "cell_type": "code", "execution_count": 27, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Bookmark( id: 'Bookmark425d97604c548aacadd8' | name: Button A | page: Sales Geography | show: ['3656b63a1c5a1d5864fb', '2caadbd8b53008045d2d'] | hide: ['a60fd9caebd151edd61b', '1aaebe320ae9e89ac467'] )" ] }, "execution_count": 27, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# After getting the list of visuals, pass it as an argument of a Bookmark\n", "button_a = ppr.Bookmark(bookmark_name='Button A',\n", " show_visuals=to_show,\n", " hide_visuals=to_hide)\n", "\n", "button_a" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Insert the bookmark using `insert_bookmark(ppr_bookmark)`" ] }, { "cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [], "source": [ "# This is an example of a bookmark that shows the bar chart and funnel and hides \n", "# the other bar chart and the column chart.\n", "\n", "report.insert_bookmark(ppr_bookmark=button_a)" ] }, { "cell_type": "code", "execution_count": 29, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[Bookmark( id: 'Bookmark0ed6bff31aa9b8bd1871' | name: City | page: Sales Geography | show: ['3656b63a1c5a1d5864fb', 'b48b6690e2c83b1a4339', '07788b861426e801f4c7'] | hide: ['a60fd9caebd151edd61b'] ),\n", " Bookmark( id: 'Bookmark84f5abdb65c08d899418' | name: Country | page: Sales Geography | show: ['b48b6690e2c83b1a4339', 'a60fd9caebd151edd61b', '07788b861426e801f4c7'] | hide: ['3656b63a1c5a1d5864fb'] ),\n", " Bookmark( id: 'Bookmark5fa4a86292cd8509f1a7' | name: V | page: Sales Reseller | show: ['1c510b92f9664aa06f71', '74dc7d85aa39bf8a5880'] | hide: ['2ea41ed9ed62c60aab33'] ),\n", " Bookmark( id: 'Bookmark549dfb51b281266458c9' | name: P | page: Sales Reseller | show: ['74dc7d85aa39bf8a5880', '2ea41ed9ed62c60aab33'] | hide: ['1c510b92f9664aa06f71'] ),\n", " Bookmark( id: 'Bookmarkbeef068b5ca61ec2a331' | name: Botao Solto | page: Sales Geography | show: ['07788b861426e801f4c7'] | hide: ['3656b63a1c5a1d5864fb', 'b48b6690e2c83b1a4339', 'a60fd9caebd151edd61b'] ),\n", " Bookmark( id: 'Bookmark425d97604c548aacadd8' | name: Button A | page: Sales Geography | show: ['3656b63a1c5a1d5864fb', '2caadbd8b53008045d2d'] | hide: ['a60fd9caebd151edd61b', '1aaebe320ae9e89ac467'] )]" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Check if it was added\n", "report.bookmarks" ] }, { "cell_type": "code", "execution_count": 30, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Bookmark( id: 'Bookmarkffa29498e245e246f334' | name: Button B | page: Sales Geography | show: ['a60fd9caebd151edd61b', '1aaebe320ae9e89ac467'] | hide: ['3656b63a1c5a1d5864fb', '2caadbd8b53008045d2d'] )" ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# It was just one button. If we desire to create the oposite behaivor\n", "button_b = ppr.Bookmark(bookmark_name='Button B',\n", " show_visuals=to_hide, #just change the argument postion\n", " hide_visuals=to_show)\n", "\n", "button_b" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Create a bookmark groups using BookmarkGroup object" ] }, { "cell_type": "code", "execution_count": 31, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "BookmarkGroup( id: 'Bookmarkfcde09353f8b93ade6a0' | name: Buttons A and B | page: Sales Geography | bookmarks_id: ['Bookmark425d97604c548aacadd8', 'Bookmarkffa29498e245e246f334'] | bookmarks_name: ['Button A', 'Button B'] | visuals: ['3656b63a1c5a1d5864fb', '2caadbd8b53008045d2d', 'a60fd9caebd151edd61b', '1aaebe320ae9e89ac467', 'a60fd9caebd151edd61b', '1aaebe320ae9e89ac467', '3656b63a1c5a1d5864fb', '2caadbd8b53008045d2d'] )" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# With two bookmarks, we can create a bookmark group\n", "\n", "book_group = ppr.BookmarkGroup(bookmark_group_name='Buttons A and B', \n", " children_list=[button_a, button_b])\n", "\n", "book_group" ] }, { "cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [], "source": [ "# Bookmarks groups are inserted as bookmarks\n", "report.insert_bookmark(book_group)" ] }, { "cell_type": "code", "execution_count": 33, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[BookmarkGroup( id: 'Bookmark3a33e033c4557e665f3b' | name: City or Country | page: Sales Geography | bookmarks_id: ['Bookmark0ed6bff31aa9b8bd1871', 'Bookmark84f5abdb65c08d899418'] | bookmarks_name: ['City', 'Country'] | visuals: ['3656b63a1c5a1d5864fb', 'b48b6690e2c83b1a4339', '07788b861426e801f4c7', 'a60fd9caebd151edd61b', 'b48b6690e2c83b1a4339', 'a60fd9caebd151edd61b', '07788b861426e801f4c7', '3656b63a1c5a1d5864fb'] ),\n", " BookmarkGroup( id: 'Bookmark788448a1069d01cbd063' | name: V, P | page: Sales Reseller | bookmarks_id: ['Bookmark5fa4a86292cd8509f1a7', 'Bookmark549dfb51b281266458c9'] | bookmarks_name: ['V', 'P'] | visuals: ['1c510b92f9664aa06f71', '74dc7d85aa39bf8a5880', '2ea41ed9ed62c60aab33', '74dc7d85aa39bf8a5880', '2ea41ed9ed62c60aab33', '1c510b92f9664aa06f71'] ),\n", " BookmarkGroup( id: 'Bookmarkfcde09353f8b93ade6a0' | name: Buttons A and B | page: Sales Geography | bookmarks_id: ['Bookmark425d97604c548aacadd8', 'Bookmarkffa29498e245e246f334'] | bookmarks_name: ['Button A', 'Button B'] | visuals: ['3656b63a1c5a1d5864fb', '2caadbd8b53008045d2d', 'a60fd9caebd151edd61b', '1aaebe320ae9e89ac467', 'a60fd9caebd151edd61b', '1aaebe320ae9e89ac467', '3656b63a1c5a1d5864fb', '2caadbd8b53008045d2d'] )]" ] }, "execution_count": 33, "metadata": {}, "output_type": "execute_result" } ], "source": [ "report.bookmark_groups" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Adding a Bookmark navigator" ] }, { "cell_type": "code", "execution_count": 34, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "BookmarkSlicer( id: 9bf4f12e75c239cf82e3 | page: Sales Geography | hor,ver: (493,118) | h,w: (49, 259) | type: bookmarkNavigator )" ] }, "execution_count": 34, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Combine with visuals and add a bookmark navigator to the report\n", "\n", "bookmark_nav = ppr.create_new_visual(visual='bookmark_slicer', \n", " page_name='Sales Geography', \n", " page_id='ReportSectionbe016ac3584b5b3bef65')\n", "\n", "bookmark_nav" ] }, { "cell_type": "code", "execution_count": 35, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "\"'Bookmarkfcde09353f8b93ade6a0'\"" ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Add the bookamrks group for selection\n", "bookmark_nav.bookmark_group = f\"'{book_group.id}'\" \n", "# This syntax of \"'id'\" is necessary to work inside Power BI\n", "\n", "bookmark_nav.bookmark_group" ] }, { "cell_type": "code", "execution_count": 36, "metadata": {}, "outputs": [], "source": [ "# Finally, insert the navigator into report.\n", "\n", "report.insert_visual_in_page(page_name='Sales Geography', visual=bookmark_nav)" ] }, { "cell_type": "code", "execution_count": 37, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[Visual( id: 3656b63a1c5a1d5864fb | page: Sales Geography | hor,ver: (201,207) | h,w: (345, 414) | type: clusteredBarChart ),\n", " Visual( id: b48b6690e2c83b1a4339 | page: Sales Geography | hor,ver: (31,17) | h,w: (48, 339) | type: textbox ),\n", " Visual( id: a60fd9caebd151edd61b | page: Sales Geography | hor,ver: (201,150) | h,w: (358, 420) | type: clusteredBarChart ),\n", " BookmarkSlicer( id: 07788b861426e801f4c7 | page: Sales Geography | hor,ver: (201,160) | h,w: (36, 220) | type: bookmarkNavigator ),\n", " Visual( id: 2caadbd8b53008045d2d | page: Sales Geography | hor,ver: (711,106) | h,w: (201, 363) | type: funnel ),\n", " Slicer( id: a6a4486f8508b0878304 | page: Sales Geography | hor,ver: (31,63) | h,w: (144, 121) | type: slicer ),\n", " Column( id: 1aaebe320ae9e89ac467 | page: Sales Geography | hor,ver: (637,308) | h,w: (243, 571) | type: columnChart ),\n", " Card( id: 1a35ebc8b0a047328cc4 | page: Sales Geography | hor,ver: (201,66) | h,w: (61, 147) | type: card ),\n", " Column( id: 906fd8155f34f3c14afb | page: Sales Geography | hor,ver: (200,100) | h,w: (200, 571) | type: columnChart ),\n", " Card( id: 47c4cb7acaf3f4f61c2e | page: Sales Geography | hor,ver: (100,233) | h,w: (10, 172) | type: card ),\n", " BookmarkSlicer( id: 9bf4f12e75c239cf82e3 | page: Sales Geography | hor,ver: (493,118) | h,w: (49, 259) | type: bookmarkNavigator )]" ] }, "execution_count": 37, "metadata": {}, "output_type": "execute_result" } ], "source": [ "report.pages_visuals['Sales Geography']" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Save changes **as a new report**" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Utilize the `save_report` function from the report to save the modifications and consolidate the changes into a new file" ] }, { "cell_type": "code", "execution_count": 38, "metadata": {}, "outputs": [], "source": [ "report.save_report(file_name='../../example/Sample Report Edited.pbix')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "---" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Working with Power BI model" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**The Power BI model only exists when the file is opened**. Therefore, all the code here assumes that a Power BI file is open." ] }, { "cell_type": "code", "execution_count": 56, "metadata": {}, "outputs": [], "source": [ "# Use PBIModel to define the model objects\n", "model = ppr.PBIModel('Sample Report.pbix') #the arg is the path of pbix file" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Model information" ] }, { "cell_type": "code", "execution_count": 43, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The port of report is: 62731\n", "Tables of the model: ['SalesTerritory', 'SalesOrder', 'Sales', 'Reseller', 'Product', 'Date', 'Customer', 'Metrics']\n", "Tabular model object: Microsoft.AnalysisServices.Tabular.Model\n" ] } ], "source": [ "# Get some information from model\n", "\n", "print(f'The port of report is: {model.port_number}')\n", "print(f'Tables of the model: {model.tables}')\n", "print(f'Tabular model object: {model.model}')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "When initiated, all tables in the model are scanned along with their columns and measures, and they are turned into attributes.\n", "\n", "Table attributes initiate with `t_`, columns with `c_`, and measures with `m_`. \n", "It's important to be aware that the names of attributes may be quite different from fields names due to character restrictions.\n" ] }, { "cell_type": "code", "execution_count": 44, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Metrics table: 'Metrics'\n", "Sales metric: 'Metrics'[Sales]\n", "Costumer table: 'Customer'\n", "Costumer ID column: 'Customer'[Customer ID]\n" ] } ], "source": [ "# Explorer tables, columns and measures attributes\n", "print(f'Metrics table: {model.t_metrics}')\n", "print(f'Sales metric: {model.t_metrics.m_sales}')\n", "\n", "print(f'Costumer table: {model.t_customer}')\n", "print(f'Costumer ID column: {model.t_customer.c_customer_id}')" ] }, { "cell_type": "code", "execution_count": 45, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The table of the column: Customer\n", "The qualified name: 'Customer'[Customer ID]\n", "The string for fields: Customer.Customer ID\n", "\n", "\n", "The table of the measure: Metrics\n", "The qualified name: 'Metrics'[Sales]\n", "The string for fields: Metrics.Sales\n", "The DAX formula: SUM(Sales[Sales Amount])\n" ] } ], "source": [ "# Some attributes of columns\n", "print(f'The table of the column: {model.t_customer.c_customer_id.table_name}')\n", "print(f'The qualified name: {model.t_customer.c_customer_id.qualified_name}')\n", "print(f'The string for fields: {model.t_customer.c_customer_id.field_name}')\n", "print('\\n')\n", "\n", "# Some attributes of measure\n", "print(f'The table of the measure: {model.t_metrics.m_sales.table_name}')\n", "print(f'The qualified name: {model.t_metrics.m_sales.qualified_name}')\n", "print(f'The string for fields: {model.t_metrics.m_sales.visual_field_name}')\n", "print(f'The DAX formula: {model.t_metrics.m_sales.dax}')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "There are functions to summarize the measures, tables, and columns. The return of these functions is a Pandas DataFrame." ] }, { "cell_type": "code", "execution_count": 59, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
DescriptionData type
Table NameNumber of MeasuresMeasure Name
Metrics1SalesCreated with pprDouble
\n", "
" ], "text/plain": [ " Description Data type\n", "Table Name Number of Measures Measure Name \n", "Metrics 1 Sales Created with ppr Double" ] }, "execution_count": 59, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Resume measures dataframe\n", "model.resume_measures().head(3).iloc[:,:2]" ] }, { "cell_type": "code", "execution_count": 61, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
DescriptionData type
Table NameNumber of ColumnsColumn Name
SalesTerritory5RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61Int64
SalesTerritoryKeyInt64
RegionString
\n", "
" ], "text/plain": [ " Description \\\n", "Table Name Number of Columns Column Name \n", "SalesTerritory 5 RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61 \n", " SalesTerritoryKey \n", " Region \n", "\n", " Data type \n", "Table Name Number of Columns Column Name \n", "SalesTerritory 5 RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61 Int64 \n", " SalesTerritoryKey Int64 \n", " Region String " ] }, "execution_count": 61, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Resume table and columns dataframe\n", "model.resume_tables_and_columns().head(3).iloc[:,:2]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Add a measure in model using `add_measure_in_model`" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "I believe one of the greatest benefits of ppr Model is the ability to add measures to the Power BI model using Python. \n", "\n", "It can be considered an **alternative to Tabular Editor scripts**. \n", "In Tabular Editor, for iterations and other complicated actions, you need to know `C#`. \n", "Here, you can use Python." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "To begin, three arguments are necessary to add a measure: the **table name**, the **name**, and the **expression**." ] }, { "cell_type": "code", "execution_count": 62, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Measure Sales in Europe was added in table Metrics in the model\n" ] } ], "source": [ "# First, let's create the measure\n", "name = 'Sales in Europe'\n", "expression = '''\n", "CALCULATE ( [Sales], SalesTerritory[Group] = \"Europe\" )\n", "'''\n", "\n", "# So, use the function to add into `metrics` table\n", "model.add_measure_in_model(table_name='Metrics', name=name, \n", " expression=expression)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "As it run, the measure is added directly to Power BI: \"image\" " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Besides the name and the expression, format string, display folder and description can be passed to function. \n", "The other important argument of function is `if_exists`. If measure already exists in the model, a warn is returned. If you are sure that measure can be overwriten, then set if_exists to 'delete'\n", "\n", "\n", "Besides the name and the expression, format string, display folder, and description can be passed to the function. \n", "\n", "Another important argument of the function is `if_exists`. \n", "\n", "If the measure already exists in the model, a warning is returned. If you are sure that the measure can be overwritten, then set if_exists to 'delete'." ] }, { "cell_type": "code", "execution_count": 63, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Measure Sales in Europe was added in table Metrics in the model\n" ] } ], "source": [ "# Define the format string, description and folder of the measure\n", "format_string = '#,0'\n", "description = 'Sales for Europe region'\n", "display_folder = r'Sales\\Territory'\n", "\n", "# Add to model\n", "model.add_measure_in_model(table_name='Metrics', name=name, \n", " expression=expression, format_string=format_string,\n", " description=description,\n", " display_folder=display_folder, if_exists='delete')\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The folder is created and format is as we set.\n", "\n", "![img](./_static/metricfolder.png) \n", "![img](./_static/metricformat.png)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "With this function, you can add a batch of measures in a loop inside Python or use an external file, like Excel. \n", "\n", "To assist with this, the model has a function to export a file called `ppr_measure_creator`." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Excel file ppr_measure_creator.xlsx saved in the folder\n" ] } ], "source": [ "# Use excel_measure creator and gain a file to organize your measures\n", "model.export_excel_measure_creator()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "An Excel file is saved inside the working directory with the name `ppr_measure_creator.xlsx`. \n", "Inside this file, the sheet Measure Creator already contains the main column for measure addition. \n", "\"image\"\n", "\n", "The objective is to facilitate the organization of measures and their addition to the model. \n", "Let's add some rows into the table and demonstrate how to read it with Pandas and iterate over it. \n", "\n", "\"image\"" ] }, { "cell_type": "code", "execution_count": 66, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Measure Sales YTD was added in table Metrics in the model\n", "Measure Sales MTD was added in table Metrics in the model\n" ] } ], "source": [ "# Import pandas\n", "import pandas as pd\n", "\n", "# Read the sheet\n", "df_measure_creator = pd.read_excel('../../example/ppr_measure_creator.xlsx', #to reproduce\n", " sheet_name='Measure Creator')\n", "\n", "# Iterate rows and use it as arguments of add_measure_in_model\n", "for idx, row in df_measure_creator.iterrows():\n", "\n", " model.add_measure_in_model(table_name=row['Table'], name=row['Name'], \n", " expression=row['Expression'], \n", " format_string=row['Format String'],\n", " description=row['Description'],\n", " display_folder=row['Display Folder'], \n", " if_exists='delete')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Be aware not to run the function `export_excel_measure_creator` again and overwrite the file with measures. \n", "I typically rename this file when it's created." ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.11.3" }, "orig_nbformat": 4 }, "nbformat": 4, "nbformat_minor": 2 }