Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

The 'How to Guide' to Power BI

JordanB
Alteryx
Alteryx
Created

The Publish to Power BI Tool has been deprecated and replaced with the Power BI Output Tool. See here for more information about the Power BI Output Tool: https://help.alteryx.com/current/MicrosoftPowerBIOutput.htm The rest of this article refers to the deprecated Publish to Power BI Tool.


By combining Alteryx and Microsoft Power BI, organizations can streamline and accelerate the process of preparing and analyzing data. This provides a faster way to deliver an end-to-end experience for data access, preparation, analysis, visualization and consumption — delivering deeper business insight faster with a more complete set of data.

With the new macro connecting to Power BI within Alteryx is even easier!

Depending upon your intentions with the Power BI tool you may need to sign up for Power BI and Microsoft Azure or just Power BI. If you are only using Power BI independently then Option 1 will be fine, however if you have corporate deployment of Microsoft Azure then Option 2 would be best.

Option 1:

This option is ideal if you would like to manipulate your data in Alteryx and then use the visualization methods inside Power BI. This set up is perfect if you are not looking to manage users or connect to local or network directories.

Option 2:

This option if for a cooperate deployment who utilize the Microsoft Azure. To use the Alteryx application it needs to be registered by your Azure administrator and a Client ID obtained from them. When using an account you setup yourself, you'llneed to perform the following Azure Active Directory administration tasks to obtain the Client ID. It is recommended to get help from someone familiar with Azure Active Directory management, however the links below give good step by steps guides on how to set this up.

Once you have selected your option let'smove over to the Designer!

Step 1: Let'smove to Alteryx

Pic 3.png

  • You can now drag the macro onto the canvas and use it to upload your modifieddata to Power BI.

Step 2: Configuration – Dataset Name

  • Enter the dataset name as you want it to appear in Power BI

Step 3: Configuration – Table Name

  • Enter that table name as you want it appear in Power BI

Step 4: Operation

  • Create New: This will create a new dataset/table. If the dataset already appears it will append ‘_2” to the dataset name.
  • Append: This will append records to the existing table. (Dataset name has to be exact)
  • Replace: This will overwrite the existing table. (Dataset name has to be exact)

Step 5: Advanced Tab

  • Interactive authorization: This option will prompt the user for their username and password for Power BI. If you are looking to schedule this workflow or upload to the Gallery please choose ‘Hard-coded credentials’ because the interactive authentication will not be able to retrieve the client ID.
  • Refresh token: This option caches a ‘refresh token’ which allows the user to re-run the workflow without the needs to re-authenticate until the refresh token expires. (Check with Microsoft for expiration policies). Uncheck this box if attempting to publish to a different Power BI account so that the user will be prompted for a different username. Unchecking this box and running the workflow will delete the cached refresh token which could be useful if the token becomes corrupted.
  • Hard-coded credentials: Please enter your Power BI Username; Password; and client ID. To obtain. This is the ideal choice if you are looking to schedule or run this workflow on the gallery.

Step 6: Run the workflow!

  • You can now run the workflow and Publish to Power BI.

Tips and Tricks

  • Our best practice is to select ‘Work or school account’
  • Power BI has about a 2GB limit in the cloud, so clean it up now and then.

Common Errors

Publish to Power BI (2) Tool #2: The external program "PBIGetAccessToken.exe" returned an error code: -532462766.

Pic 7 PBInotgetting.png

  • This error is related to write permissions when writing out the access token. This error should be fixed in the latest release on theMacro District
  • As the Tips and Tricks mentioned please select ‘Work or school account’ and enter your credentials. This will prompt a new permissions window. Please hit ‘Accept’ and you should be able to write out the access token correctly.

pic 10.png

  • If you continue to get this error please run Alteryx as Administrator.

Error: Publish to Power BI (48): Tool #4: The type initializer for 'Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext' threw an exception.

  • Please check your .NET Framework (the easiest way to find out is probably to navigate to your installed programs via control panel), this tool requires a .NET 4.5 framework.

[Publish to Power BI (1) Tool #4: Loading an assembly required for interactive user authentication failed. Make sure assembly 'Microsoft.IdentityModel.Clients.ActiveDirectory.WindowsForms, Version=2.12.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' exists]

  • This issue occurs when one of the dependent files (Microsoft.IdentityModel.Clients.ActiveDirectory.WindowsForms.dll) is missing or corrupt. Re-installing the tool should resolve this issue.

Power BI API error….HTTP/1.1 400 (‘error”(code…….

Pic 6 - Power BI API.png

  • Open PBIX from Power BI webapp, then try to use macro to append/replace dataset of same name as PBIX results in error. The solution is to use a different dataset name.
  • This is a bug in our JSON Build tool please do not have fields containing a period

The browser based authentication dialog failed to complete. Reason: The download has failed (the connection was interrupted)

Pic 8 browser based.png

  • Please enable TLS in IE advanced settings within internet options.

Pic 5.png

Authentication failed: Refresh Token is malformed or invalid

Pic 9.png

  • The refresh token is corrupted, please delete the refresh token in the same folder of the macro or uncheck refresh token in macro interface.

Pic 4.png

An Array can’t be at the top level

Array.png

  • This is a bug with our JSON build tool please do not start with a number

If you continue to have problems with the Power BI Macropleasecontact Alteryx Support.

Best,

Jordan Barker

Professional Services

Comments
Musa
5 - Atom

Hi, I am experiencing the error with the power bi JSON bug , 'An array can't be at the top level.' My field/column names do not begin with a number, however the rows contain numbers. How do I fix this? Thank you! 

charlep_dup_424
6 - Meteoroid

Is there any way to publish data direct to a PowerBI App workspace (rather than My Workspace) - for example if I am working collaboratively with a team?

Martin_Miller
6 - Meteoroid

I would agree with @charlep ... is there a way within the Macro to define which Workspace to publish the PowerBI Data set?

JordanB
Alteryx
Alteryx

Hi @charlep_dup_424 & @Martin_Miller

 

This is not available in the 2018.2 release of Alteryx Designer.

 

https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Save-Workspace/idi-p/141865

 

Best,

 

Jordan

charlep_dup_424
6 - Meteoroid

Hi Jordan

 

I think the link you provided is for saving your Alteryx workspace including all the workflows. We are asking if it's possible to push to a specific PowerBI Online (Microsoft SaaS) data store, which is something different.


Cheers

Charlie

ARich
Alteryx Alumni (Retired)

Hi @charlep_dup_424,

 

No. With the existing Publish to Power BI Tool, it is not possible to specify a workspace other than the default ‘My Workspace.’ However, we are looking at providing the option to select another workspace in the next version of the PowerBI tool.

 

Best,

Alex

MornayBartlett
6 - Meteoroid

Hi ,

 

I received the following error:

Error: PublishToPowerBI (1): Tool #4: <HTML><HEAD>

JordanB
Alteryx
Alteryx

Hi @MornayBartlett

 

Tool 4 in the macro suggests that the client ID, Secret is not valid. Are you using this option?

 

Best,

 

Jordan

MornayBartlett
6 - Meteoroid
Hi Jordan,

I am using the following option:



Thanks

--


PwC – a triple A + level 1 contributor, committed to ongoing
transformation.


The information transmitted is intended only for the
person or entity to which it is addressed and may contain confidential
and/or privileged material. Any review, retransmission, dissemination or
other use of, or taking of any action in reliance upon, this information by
persons or entities other than the intended recipient is prohibited. If you
received this in error, please contact the sender and delete the material
from any computer.
JordanB
Alteryx
Alteryx

Sorry @MornayBartlett I cannot see the image.

 

Best,

 

Jordan

MornayBartlett
6 - Meteoroid

Hi Jordan,

I am using the interactive authorization option.

Regards
Mornay

rexeven
6 - Meteoroid

I just wanted to add my vote for allowing to save to different workspaces with the PowerBI Connector.  Is there a place I can go to add the idea or vote for it?

DanM
Alteryx Community Team
Alteryx Community Team

@rexeven,

 

You can go to the Designer Ideas board and post your idea. Alteryx-Designer-Ideas

 

Thanks

DanM

florebrunialti
6 - Meteoroid

Hi team,

 

Is there any possibility to extract information from a Power BI database and bring it to my Alteryx?

 

Thanks in advance!

charlep_dup_424
6 - Meteoroid

@forebrunialti - what do you mean by extract information from a Power BI database?

 

When you publish to PowerBI, it sits as a dataset in the Power BI Service (effectively I believe it's SQL Server Analysis Services repurposed).

 

You can access it from Excel as a data source, but I believe at the moment the feature set isn't quite the same as SSAS so you can't consume the PowerBI data in e.g. Alteryx.


The best bet if you need both, is to push the data into a SQL Database, and then from there into your PowerBI model. That way you have the DB data as well as the cube - you won't be able to get any of the additional measures you've created in the PBI Cube though, so these will need to be pushed into the DB.

The other alternative is to push the data into SSAS / Azure Analysis Services Tabular Cube, and from there into PBI (and Alteryx)

 

Cheers

Charlie

Hiroy
7 - Meteor

Hi,

 

I really agree that specifying the Power BI Service workspace to publish to is a critical feature that needs to be developed in the next version of Power BI tool.

I also think that publishing multiple tables in 1 Power BI dataset is needed. Because Power BI can contain multiple tables in 1 dataset and use relationships to connect the tables, the current Power BI tool that can only have 1 table in 1 dataset is a big restriction.

 

Thanks

Hiro

ClaudiaV
7 - Meteor

Hi

I was able to create a new dataset/table with the Interactive authorization

However it didn't let me Replace.

 

I got an error like the table doesn't exist

error.PNG

Can you help?

Thanks

 

JeMe
5 - Atom

Hi,

I don't seem to be having any luck getting the Power Bi connector button on my connectors page.  I have downloaded the macro and can see that the macro has saved in the place I have specified however that's all I can see.  I don't get the grey icon in my connectors tab.

Do you know why this may be the case?

Thank you if you can help.

 

 

ClaudiaV
7 - Meteor

Hi
Try restarting your computer

JeMe
5 - Atom

Hi Jordan,

I am getting the error, "The browser based authentication dialog failed to complete. Reason: The server or proxy was not found", when trying to publish to Power Bi. Do you by chance know why?  I have spoken to our IT Helpdesk and they do not have a solution.   Hoping you can help.

Kind regards

JeMe

Prashant_Iyer
5 - Atom

I tried searching for "Publish to Power BI Tool" in Alteryx Gallery, But I was not able to find it. Instead I found "Power BI" output. Are both of these the same?

 

NeilR
Alteryx Alumni (Retired)

@Prashant_Iyer the Publish to Power BI Tool has just recently been deprecated and replaced with the Power BI Output Tool. https://help.alteryx.com/current/MicrosoftPowerBIOutput.htm

MartinJuguera
6 - Meteoroid
I need help i jus reinstall the tool and it doesn't work anymore, i don't have the option to choose "Hardcoded Credential" or "Interactive autorization" and i have also the following error : Traceback File "main.py", line 3, in ModuleNotFound Error : No module named 'power_by_output' How can i solve this problem? Thanks
RCL1
6 - Meteoroid

@MartinJuguera - I'm having the same problem with the new tool.

mchamps
7 - Meteor

I'm getting the following error:

 

Error: Power BI Output (3): Traceback (most recent call last):
File "C:\Users\[USERNAME]\AppData\Roaming\Alteryx\Engine\../Tools\power_bi_output_v2.1.1\power_bi_output\connections.py", line 84, in ii_init
token_updater=self._update_token
File "C:\Users\[USERNAME]\AppData\Roaming\Alteryx\Engine\../Tools\power_bi_output_v2.1.1\Lib\site-packages\requests_oauthlib\oauth2_session.py", line 69, in __init__
self.token = token or {}
File "C:\Users\[USERNAME]\AppData\Roaming\Alteryx\Engine\../Tools\power_bi_output_v2.1.1\Lib\site-packages\requests_oauthlib\oauth2_session.py", line 119, in token
self._client.populate_token_attributes(value)
AttributeError: 'AzureActiveDirectoryWebApplicationClient' object has no attribute 'populate_token_attributes'
JordanB
Alteryx
Alteryx

Hi @MartinJuguera & @mchamps

 

Please reach out to support@alteryx.com to log this issue

 

Best,

 

Jordan

rajaniesh
5 - Atom

I am also getting the exactly same error 

 

Error: Power BI Output (3): Traceback (most recent call last):
File "C:\Users\[USERNAME]\AppData\Roaming\Alteryx\Engine\../Tools\power_bi_output_v2.1.1\power_bi_output\connections.py", line 84, in ii_init
token_updater=self._update_token
File "C:\Users\[USERNAME]\AppData\Roaming\Alteryx\Engine\../Tools\power_bi_output_v2.1.1\Lib\site-packages\requests_oauthlib\oauth2_session.py", line 69, in __init__
self.token = token or {}
File "C:\Users\[USERNAME]\AppData\Roaming\Alteryx\Engine\../Tools\power_bi_output_v2.1.1\Lib\site-packages\requests_oauthlib\oauth2_session.py", line 119, in token
self._client.populate_token_attributes(value)
AttributeError: 'AzureActiveDirectoryWebApplicationClient' object has no attribute 'populate_token_attributes'

 

 

Can Alteryx support team can help here.

 

Regards
Rajaniesh

JessShan
5 - Atom
Hi! I got an error says: transferring data error--couldn't resolve host name. I've signed up power bi account already and got client ID. Any way to solve this issue? Best regards, J
JordanB
Alteryx
Alteryx

@JessShan 

 

Can you access powerbi online? This usually suggests firewall issues as Alteryx cannot talk to the website

 

Best,

 

Jordan

jmurillo22
5 - Atom

Hi Jordan,

 

This connector  is designed to connect to a Power BI server hosted by Azure but has not ability to connect to your local Power BI server.

 

When compare to Tableau connector it seems this connector has not evolved as much in recent years but Power BI is growing fast in the market. 

 

Do you know if there are any plans to include the feature to connect and publish to a local Power BI server?

 

Thanks,

 

TrevorS
Alteryx Alumni (Retired)

Hello @jmurillo22


Thanks for your feedback on this knowledge piece. I would recommend that you create an official Idea under the Designer- Idea section making this exact request. If you know anyone else who feels the same way please make sure you have them visit your post and star it!

 

Thanks!

TrevorS