topic Re: Adaptive Insight Connector in Developer

Adaptive Insight Connector

houston39 — Thu, 09 Jan 2020 20:45:47 GMT

Hi - I am curious if anyone has a custom connector to Adaptive Insights? I have been waiting too long for Microsoft to come up with a solution.

Re: Adaptive Insight Connector

rtaylor — Tue, 14 Jan 2020 22:43:30 GMT

There isn't one, but depending on your login credetials you may be able to create it in power query. This does not work if using Azure Active Directory

---------------------------------------------------------

I recently was able to connect Power BI to our Adaptive instance using the attached pdf as guidance. You probably need to tweak this for Tableau. The only tweak I had to do to the xml section was to double the "double quotes". The rest of the code are Power BI specific code (M Language).

let

Source = Xml.Tables(

Web.Contents("https://api.adaptiveinsights.com/api/v14",

[Content = Text.ToBinary("<?xml version='1.0' encoding='UTF-8'?>

</accounts>

</filters>

</dimensions>

</rules>

</call>")

])),

CSV = Table.SelectColumns(Source,{"output"}),

#"Split Column by Delimiter (Rows)" = Table.ExpandListColumn(Table.TransformColumns(CSV, {{"output", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "output"),

#"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter (Rows)", "output", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"output.1", "output.2", "output.3", "output.4", "output.5", "output.6", "output.7", "output.8", "output.9", "output.10", "output.11", "output.12", "output.13", "output.14", "output.15", "output.16", "output.17", "output.18", "output.19", "output.20", "output.21", "output.22", "output.23", "output.24", "output.25", "output.26", "output.27", "output.28", "output.29", "output.30", "output.31", "output.32", "output.33", "output.34", "output.35", "output.36", "output.37", "output.38", "output.39", "output.40", "output.41", "output.42", "output.43", "output.44", "output.45", "output.46", "output.47", "output.48", "output.49", "output.50", "output.51", "output.52"}),

#"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter1", [PromoteAllScalars=true]),

#"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Account Name", "Account Code", "Level Name", "Project Allocation", "Jan-2018", "Feb-2018", "Mar-2018", "Apr-2018", "May-2018", "Jun-2018", "Jul-2018", "Aug-2018", "Sep-2018", "Oct-2018", "Nov-2018", "Dec-2018", "Jan-2019", "Feb-2019", "Mar-2019", "Apr-2019", "May-2019", "Jun-2019", "Jul-2019", "Aug-2019", "Sep-2019", "Oct-2019", "Nov-2019", "Dec-2019"}),

#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Account Name", "Account Code", "Level Name","Project Allocation"}, "Attribute", "Value"),

#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Period"}}),

#"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each [Value] <> "0.0"),

#"Sorted Rows" = Table.Sort(#"Filtered Rows1",{{"Value", Order.Ascending}}),

#"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Period", type date}, {"Value", type number}}),

#"Calculated End of Month" = Table.TransformColumns(#"Changed Type",{{"Period", Date.EndOfMonth, type date}}),

#"Added Account Column" = Table.AddColumn(#"Calculated End of Month", "Account", each Text.Start([Account Code], 5), type text),

#"Changed Type1" = Table.TransformColumnTypes(#"Added Account Column",{{"Account", Int64.Type}}),

#"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type1", {"Account"}),

#"Added Dept Column" = Table.AddColumn(#"Removed Errors", "Dept", each Text.Start([Level Name], 6), type text),

#"Added Version Column" = Table.AddColumn(#"Added Dept Column", "Version", each "Current Forecast"),

#"Changed Type2" = Table.TransformColumnTypes(#"Added Version Column",{{"Account", type text}, {"Version", type text}})

#"Changed Type2"

Re: Adaptive Insight Connector

houston39 — Tue, 14 Jan 2020 22:49:41 GMT

Hi - I do not see a .pdf file attached? Thank you..

Re: Adaptive Insight Connector

rtaylor — Tue, 14 Jan 2020 22:58:36 GMT

I must not have access to attach a file. Here is the website

https://knowledge.adaptiveplanning.com/Integration/Managing_Data_Integration/API_Documentation

Re: Adaptive Insight Connector

houston39 — Wed, 15 Jan 2020 15:36:34 GMT

Thank you!

Re: Adaptive Insight Connector

keeganm — Mon, 20 Apr 2020 19:44:06 GMT

I was able to use this coding to get my connection to work. The one issue I am having is the time span is not filtering. I end up with every month of the version I am pulling.

Re: Adaptive Insight Connector

rtaylor — Mon, 20 Apr 2020 19:51:06 GMT

That shouldbn't be a problem though right? You should be able to use power query and filter the dates you need after your api request is complete.

Re: Adaptive Insight Connector

keeganm — Tue, 21 Apr 2020 23:46:21 GMT

You can, it just not as efficent. I am using version 23 of the API and I found instead of using Jan-2020 I had to use 01/2020.

Re: Adaptive Insight Connector

Anonymous — Mon, 02 Nov 2020 17:49:16 GMT

I cannot get the code to work. I have updated the credentials but I'm unsure of what other edits I need to make. Could you help me out? Is it possible to share updated code, perhaps to the current version API code where you highlight the places edits are necessary?

Any help, much appreciated.

Re: Adaptive Insight Connector

Anonymous — Tue, 03 Nov 2020 00:18:20 GMT

@Anonymous ,

Please make sure you have correctly replaced the underlined items below with what theyare for your Adaptive instance/configuration. Also, please note the use of double "double quotes" here. ("" instead of ")

</accounts>

</filters>

</dimensions>

Regards,

Ferdinand

Re: Adaptive Insight Connector

jordancole0517 — Thu, 20 May 2021 21:18:12 GMT

Hello!

Hopefully this message makes it to you even though this is an old topic! Classics never go out of style, though.

I'm trying to get this to work but am getting the error below after entering in the code (changed as advised). Any ideas?

"Expression.Error: The column 'Output' of the table wasn't found.
Details:
Output"

THANK YOU!!

Re: Adaptive Insight Connector

Anonymous — Tue, 15 Nov 2022 23:50:38 GMT

hello,

I followed the steps above but without the M code after the line in CSV = Table.SelectColumns(Source,{"output"}). Power query returns a message "Illegal Input. Input data must be an XML document." and "invalid-xml". Is it because i did not specify which table to export the XML data to? Do i need to create a table call "Output" with output columns 1 to 52?

Thanks for your help

Re: Adaptive Insight Connector

venusbug — Thu, 15 Dec 2022 03:54:29 GMT

Have you managed to figure out? I've got the same error as you.

Re: Adaptive Insight Connector

keeganm — Thu, 15 Dec 2022 17:51:26 GMT

Yes the output is a essiesitial piece, this takes the data and puts it into a csv file that Power BI can then dissect. I would recommend copying all the steps and then delete the steps that you don't want to use. This connection is a little old now, so you really only need the steps down to Promote Headers. A couple other things to note:

1. Adaptive is now on version 35 of the API so version 14 may not work

2. Instead of "Include Rollups" you will want to use the following, <rules includeZeroRows=""true"" includeRollupAccounts=""false"" includeRollupLevels=""false"" markInvalidValues=""false"" markBlanks=""false"" timeRollups=""false"">

3. I found that I had to change the timespan to <timeSpan start=""05/2020"" end=""05/2020""/>

4. Make sure to save your power bi file in a secure location since you will have a password saved into the code.

Hope this helps

Re: Adaptive Insight Connector

jordancole0517 — Thu, 15 Dec 2022 20:02:53 GMT

I've tried to do many of the various suggestions but still coming up short. This would be such a game changer if it worked. Can somebody run the code below and update their credentials, please? It'd help me rule out the privacy / credentials issue on my end.

let
Source = Xml.Tables(
Web.Contents("https://api.adaptiveinsights.com/api/v35",
[Content = Text.ToBinary("<?xml version='1.0' encoding='UTF-8'?>
<call method=""exportData"" callerName=""PowerBI"">
<credentials login=""username"" password=""password"" instanceCode=""instance""/>
<version name=""Version""/>
<format useInternalCodes=""true"" includeUnmappedItems=""false""/>
<filters>
<accounts>
<account code=""Expenses"" isAssumption=""false"" includeDescendants=""true""/>
</filters>

<rules includeZeroRows=""true"" includeRollupAccounts=""false"" includeRollupLevels=""false"" markInvalidValues=""false"" markBlanks=""false"" timeRollups=""false""> <currency useCorporate=""false"" useLocal=""false"" override=""USD""/>
</rules>
</call>")
])),
CSV = Table.SelectColumns(Source,{"output"}),
#"Split Column by Delimiter (Rows)" = Table.ExpandListColumn(Table.TransformColumns(CSV, {{"output", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "output"),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter (Rows)", "output", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"output.1", "output.2", "output.3", "output.4", "output.5", "output.6", "output.7", "output.8", "output.9", "output.10", "output.11", "output.12", "output.13", "output.14", "output.15", "output.16", "output.17", "output.18", "output.19", "output.20", "output.21", "output.22", "output.23", "output.24", "output.25", "output.26", "output.27", "output.28", "output.29", "output.30", "output.31", "output.32", "output.33", "output.34", "output.35", "output.36", "output.37", "output.38", "output.39", "output.40", "output.41", "output.42", "output.43", "output.44", "output.45", "output.46", "output.47", "output.48", "output.49", "output.50", "output.51", "output.52"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter1", [PromoteAllScalars=true])
in
#"Promoted Headers"

I'm still getting the same error:

Expression.Error: The column 'output' of the table wasn't found.
Details:
output

Attached is what my PowerQuery view looks like after pasting in the code.

Re: Adaptive Insight Connector

keeganm — Thu, 15 Dec 2022 20:26:59 GMT

You are missing the ending to acccount and a timespan

take a look at this example below:

</accounts>

</filters>

Also, I haven't used the instance code before, it might be good to have, but I haven't needed it.

Re: Adaptive Insight Connector

jordancole0517 — Thu, 15 Dec 2022 20:31:01 GMT

Unfortunately that is not it. I get the same error when that is included. Those filters appeared to just be extra conditions so I thought to remove the date portion, as people had denoted issues with the formatting in other replies in the thread.

Re: Adaptive Insight Connector

keeganm — Thu, 15 Dec 2022 20:36:08 GMT

Try copying and pasting the code below, it worked for me. The other thing to double check is that the username and password have direct login access

let

Source = Xml.Tables(

Web.Contents("https://api.adaptiveinsights.com/api/v35",

[Content = Text.ToBinary(

"<?xml version='1.0' encoding='UTF-8'?>

</accounts>

</filters>

</rules>

</call>")

])),

CSV = Table.SelectColumns(Source,{"output"}),

#"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter1", [PromoteAllScalars=true])

#"Promoted Headers"

Re: Adaptive Insight Connector

Anonymous — Fri, 16 Dec 2022 02:30:49 GMT

Hi,

I managed to make it work using Keeganm's code he pasted but had to modfiy the filters.

For me, the filter for account code=""Expenses" did not work for me so i removed this filter altogether to see what comes up. I added this level filter so that it doesnt return everything;

You can try removing the account filter and see if it returns the full data and then apply filters in Power Query itself i.e. filter to account level begining with 5*.

Re: Adaptive Insight Connector

Rabi — Tue, 24 Jun 2025 02:01:54 GMT

Hello Everyone,

I am glad to let you know all. I was sucessfully able to connect to Adaptive via the XML query.

Let me know if you would like to learn on how to configure the query.

Regards,

Rabi