Last active
May 17, 2021 21:03
-
-
Save BryantAvey/4b51eacdfc57b5097fdf01fbbcdd0493 to your computer and use it in GitHub Desktop.
Import Neo4j to Power BI using a named property Cypher query. This uses the default HTTP API with Neo4j.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// This Power Query connects to Neo4j returning data from a Cypher query that returns named properties id(n), n.name, etc. | |
// Get Properties and Column Names | |
let | |
//Enter the base URL for the Neo4j Server | |
__Neo4jURL = "http://localhost:7474", | |
// Enter the name of the database in Neo4j | |
__Neo4jDatabase = "blogs", | |
// Enter the username for database access in the Neo4j database | |
__Username = "neo4j", | |
// Enter the user password for databases in the Neo4j database | |
__Password = "demo", | |
// Enter a Cypher Query returning full nodes. | |
__CypherQuery = "MATCH (a)-[r:IN_CATEGORY]-(c) RETURN id(r) as RelationshipId, id(a) as ArticleNodeId, id(c) as CategoryNodeId, type(r) as RelationshipName", | |
// This step makes the HTTP API call to Neo4j to return data in JSON format. | |
#"Neo4jResults" = | |
Json.Document( | |
Web.Contents(__Neo4jURL & "/db/" & __Neo4jDatabase & "/tx/commit", | |
[ | |
Headers = | |
[ | |
#"Content-Type"="application/json", | |
#"Authorization"="Basic " & Binary.ToText(Text.ToBinary(__Username & ":" & __Password, null, null)), | |
#"X-Stream"="true" | |
], | |
Content = Text.ToBinary("{""statements"": [ {""statement"": """ & __CypherQuery & """ } ] }") | |
] | |
) // This is the end of the Web.Contents container that makes the Neo4j HTTP API POST call. | |
)[results]{0}, // Load the [columns] and [data] sections of the Neo4j JSON results | |
// Gets Data from the Neo4j Results With Column Headers | |
DataWithColumnHeaders = Table.PromoteHeaders( | |
Table.InsertRows( | |
Table.RenameColumns( | |
Table.SplitColumn( | |
Table.TransformColumns( | |
Table.ExpandRecordColumn( | |
Table.FromList(#"Neo4jResults"[data], // Use the Data section of the Neo4j JSON results | |
Splitter.SplitByNothing(), null, null, ExtraValues.Error), //This ends converting the list to a table | |
"Column1", {"row"}, {"row"}), //This ends the expandRecordColumn function | |
{"row", each Text.Combine(List.Transform(_, Text.From), "~|~"), type text}), //This ends the TransformColumns function | |
"row", Splitter.SplitTextByDelimiter("~|~", QuoteStyle.Csv)), //This ends the Split Column function | |
//This section artifically creates 39 columns and renames them match the column header values. If more than 39 properies are needed from a Node, more can be added. | |
{{"row.1", "Column1"}, {"row.2", "Column2"}, {"row.3", "Column3"}, {"row.4", "Column4"}, {"row.5", "Column5"}, {"row.6", "Column6"}, {"row.7", "Column7"}, {"row.8", "Column8"}, {"row.9", "Column9"}, {"row.10", "Column10"}, {"row.11", "Column11"}, {"row.12", "Column12"}, {"row.13", "Column13"}, {"row.14", "Column14"}, {"row.15", "Column15"}, {"row.16", "Column16"}, {"row.17", "Column17"}, {"row.18", "Column18"}, {"row.19", "Column19"}, {"row.20", "Column20"}, {"row.21", "Column21"}, {"row.22", "Column22"}, {"row.23", "Column23"}, {"row.24", "Column24"}, {"row.25", "Column25"}, {"row.26", "Column26"}, {"row.27", "Column27"}, {"row.28", "Column28"}, {"row.29", "Column29"}, {"row.30", "Column30"}, {"row.31", "Column31"}, {"row.32", "Column32"}, {"row.33", "Column33"}, {"row.34", "Column34"}, {"row.35", "Column35"}, {"row.36", "Column36"}, {"row.37", "Column37"}, {"row.38", "Column38"}, {"row.39", "Column39"}},MissingField.Ignore), //This ends the Rename Column Fuction | |
0, Table.ToRecords( | |
Table.TransformColumnTypes( | |
Table.Transpose( | |
Table.FromList( | |
#"Neo4jResults"[columns], Splitter.SplitByNothing(), null, null, ExtraValues.Error) //This ends the convert list to table function | |
), //This ends the Transpose Table function | |
{}) //This ends Changing the column types to text | |
) //This ends converting the table of records to rows | |
), //This ends inserting the column header rows to the top of the table so headers can be promoted | |
[PromoteAllScalars=true]) //This ends promoting the column headers | |
in | |
#"DataWithColumnHeaders" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment