This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Sometimes you look at the steaming pile of data before you and wonder how you’ll ever get it in the form you need. Every option seems to require a great deal of manual labor, and as a lazy– er that is , as a data blending professional , that is simply something you will not abide.
If you are developing with Alteryx's In-Database tools you may want to share the workflows with users who do not have as much database experience. Below is an example workflow to create a macro that will allow your users to select which fields are brought out of the in database tools without interacting with the in database tools directly.
The premise of the process is that you can stream out a single record of your table, then leverage a List Box tool in select mode to de-select the fields your user does not want to pull. From there, you can use Field Summary and Summarize to create a select statement that will be used to update a Dynamic Input In-DB tool.
To run in your environment, update the Connect In-DB tool to a connection that exists on your machine and start with a select all query.
To assist you in your R adventures in Alteryx, we've developed a R Tool Cheat Sheet which you can download to have as your very own. This article reviews and explains the functions included in the Alteryx - R cheat sheet.
Imagine this – you’re on vacation. You’re on a sandy beach where the sun has been relentless all day. It’s hot and you need something to cool you off. Ice cream would be perfect! The Find Nearest tool can help!
The Sample Tool allows you selectively pass patterns, block excerpts, or samples of your records (or groups of records) in your dataset: the first N, last N, skipping the first N, 1 of every N, random 1 in N chance for each record to pass, and first N%. Using these options can come in the clutch pretty often in data preparation – that’s why you’ll find it in our Favorites Category, and for good reason. While a great tool to sample your data sets, you can also use it for:
As most of us can agree, predictive models can be extremely useful. Predictive models can help companies allocate their limited marketing budget on the most profitable group of customers, help non-profit organizations to find the most willing donors to donate to their cause, or even determine the probability a student will be admitted into a given school. A well-designed predictive model can help us make smart and cost-effective business decisions.
You’re creating an app that involves dates. You want the user to be able to dynamically select the dates being used in the app, though. The tools you already know may not work. A Text Box would be too messy and allow lots of room for error. Pre-defined Drop Downs and List Boxes aren’t dynamic enough. Ah ha! What about that tool that looks like a calendar? The Date tool in the Interface category provides the perfect solution!
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.
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.
Create a Power BI Account using the 'Get Started Free' Link
Follow the steps below
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'll need 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.
Create a Power BI Account using the 'Get Started Free' Link
Create an Azure Active Directory Tenant
Register a Client App
Obtain a Client ID
If you are unsure if you would need integration between Power BI and Azure here is a useful video explaining the integration.
Follow the steps below
Once you have selected your option let's move over to the Designer!
Step 1: Let's move to Alteryx
The Power BI tool can be found in the connectors category once installed from the Macro District on the Alteryx Gallery or the The Alteryx Starter Kit for Microsoft
You can now drag the macro onto the canvas and use it to upload your modified data 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.
Publish to Power BI (2) Tool #2: The external program "PBIGetAccessToken.exe" returned an error code: -532462766.
This error is related to write permissions when writing out the access token. This error should be fixed in the latest release on the Macro 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.
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=188.8.131.52, 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…….
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)
Please enable TLS in IE advanced settings within internet options.
Authentication failed: Refresh Token is malformed or invalid
The refresh token is corrupted, please delete the refresh token in the same folder of the macro or uncheck refresh token in macro interface.
An Array can’t be at the top level
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 Macro please contact Alteryx Support.
A close relative of the Layout Tool , the Visual Layout Tool is the newest, and coolest, Reporting Tool on the block. Sporting all the badassery of the original Layout Tool in its ability to format and arrange reporting objects, the Visual Layout Tool differentiates in that it provides an intuitive, visual, interface that allows for easy drag-and-drop organization of multiple reporting object inputs. Basically, it’s the reporting tool category equivalent of upgrading from Paint to Photoshop.
Don’t know the area of your polygon? Need the length of your line? Do you want your spatial object’s X and Y coordinates? Don’t Panic! The Spatial Info tool can translate all that information and more!
If you are building a predictive model, inevitably you will want to analyze the effect that your independent variables have on your dependent variable. This article is meant to shed some light on the Alteryx-specific options for this type of analysis!
The Dynamic Replace Tool is an under-utilized tool in the Developer Toolset that is very powerful. It allows for dynamic formulas or conditions to be used in your workflow. It was first introduced in Alteryx 6.1 . It’s one of the few tools that is currently multi-threaded which makes is fast.
The humble histogram is something many people are first exposed to in grade school. Histograms are a type of bar graph that display the distribution of continuous numerical data. Histograms are sometimes confused with bar charts, which are plots of categorical variables.