Skip to content

Instantly share code, notes, and snippets.

@BryantAvey
Last active May 17, 2021 21:03
Show Gist options
  • Save BryantAvey/4b51eacdfc57b5097fdf01fbbcdd0493 to your computer and use it in GitHub Desktop.
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 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