If you use Power BI with DataHub, some of your lineage is probably missing right now \u2014 and you don’t know it.<\/p>\n\n\n\n
A common question from newcomers: does Power BI have its own SQL dialect? The short answer is no<\/strong>. Power BI connects to external databases \u2014 SQL Server, Snowflake, PostgreSQL, and others \u2014 and sends SQL queries written in that database’s native dialect<\/em>. If your Power BI report pulls from SQL Server, the SQL inside is T-SQL; if it pulls from Snowflake, it’s Snowflake SQL.<\/p>\n\n\n\n However, Power BI does have its own query language called M<\/strong> (also known as Power Query Formula Language). M wraps everything \u2014 including embedded SQL. When you write a The most important encoding rule: M replaces actual newline characters with gets stored by Power BI as:<\/p>\n\n\n\n This is where the trouble starts. The SQL itself is standard \u2014 the encoding around it is not.<\/p>\n\n\n\n In standard SQL, This is not a parser bug in sqlglot or any other SQL parser \u2014 it’s a missing preprocessing step<\/strong>. Before parsing, someone needs to decode M’s Here’s a real query from DataHub issue #11251<\/a> that demonstrates the problem:<\/p>\n\n\n\n This query references two upstream tables: SQL comments are everywhere in Power BI datasets. Developers comment out JOINs while debugging, leave notes like Any Power BI dataset with even a single commented-out JOIN or WHERE clause will produce incomplete lineage. The lineage graph shows only a fraction of the upstream tables, and DataHub gives no warning. We verified this: sqlglot handles The issue has been open since August 2024. Three users have confirmed it blocks DataHub adoption in their organizations:<\/p>\n\n\n\n “This is something very important. Especially when SQL comments are very common in PBI M-queries.” \u2014 @AntonisCSt<\/p><\/blockquote>\n\n\n\n “We also run into this issue and it is a real issue for part of our business to accept Datahub as a common catalog solution.” \u2014 @rospe<\/p><\/blockquote>\n\n\n\n The gsp-datahub-sidecar<\/a> solves this with a two-step approach:<\/p>\n\n\n\n We verified this with the exact query from the issue. First, the raw Power BI encoded form (all on one line with Without preprocessing, every SQL parser fails<\/strong> \u2014 GSP included. The With the sidecar’s preprocessing, the Both upstream tables found. All five column-level lineages recovered:<\/p>\n\n\n\n Comments correctly stripped. JOINs preserved. WHERE clause intact.<\/p>\n\n\n\n Three commands to recover your Power BI lineage:<\/p>\n\n\n\n The sidecar emits standard DataHub MCPs (Metadata Change Proposals) via the REST API \u2014 DataHub merges the lineage with your existing metadata automatically.<\/p>\n\n\n\n The This matters because SQL dialects differ in syntax \u2014 T-SQL’s The The sidecar fills these gaps without replacing DataHub’s parser \u2014 it augments it.<\/p>\n\n\n\n Disclosure: The gsp-datahub-sidecar is built by Gudu Software<\/a>, the company behind General SQL Parser and SQLFlow. The sidecar is open source under the Apache 2.0 license.<\/em><\/p>\n\n\n\n Have a SQL parsing challenge? Try SQLFlow<\/a> with your own SQL, or visit the sidecar repo<\/a> to get started.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":" Power BI encodes newlines as #(lf) in M-language SQL. When DataHub parses queries with — comments, it silently drops all subsequent JOINs from lineage. The gsp-datahub-sidecar recovers every missing relationship.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[14,25,93],"tags":[119,135,132,138,136,137,127],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nValue.NativeQuery<\/code> expression in Power BI, the SQL string lives inside an M expression, and M applies its own encoding rules.<\/p>\n\n\n\n#(lf)<\/code><\/strong> (M’s literal for “line feed”). So a query that a human writes as:<\/p>\n\n\n\nSELECT name\nFROM customers\n-- old filter\nWHERE active = 1<\/code><\/pre>\n\n\n\nselect#(lf)name#(lf)from customers#(lf)-- old filter#(lf)where active = 1<\/code><\/pre>\n\n\n\nThe Problem: Why Comments Break Lineage<\/h2>\n\n\n\n
--<\/code> marks a single-line comment: everything from --<\/code> to the next actual newline character<\/em> is ignored. But when Power BI encodes newlines as #(lf)<\/code>, those aren’t real newline characters \u2014 they’re just ordinary text. So any SQL parser that receives this encoded string sees the --<\/code> comment as running all the way to the end of the entire query<\/em>, because there is no real newline to terminate it.<\/p>\n\n\n\n#(lf)<\/code> sequences back into real newline characters. DataHub’s Power BI ingestion connector does not do this, and neither does GSP (General SQL Parser) on its own \u2014 no SQL parser should be expected to understand Power BI M-language encoding.<\/p>\n\n\n\nselect\nupper(cs.customercode) as customercode\n, cs.ear2id as ear2id\n, db.branch_rollup_name\n, db.subregion1\n, db.subregion2\nfrom nast_truckload_domain.broker.dim_customer cs\n--join ... (commented out)\njoin nast_customer_domain.broker.dim_customer_ear2_single_ownership_history as so\n on cs.ear2id = so.ear2_id and is_current = true\njoin nast_customer_domain.broker.ref_branch db\n on db.Branch_code = so.branch_code\n-- join ... (commented out)\nwhere cs.customerstatusid = 1 --active\nand db.primary_business_line_id in ('62','73')<\/code><\/pre>\n\n\n\ndim_customer<\/code> and ref_branch<\/code>. But when Power BI encodes it with #(lf)<\/code> and DataHub’s parser reads it, the --join<\/code> comment on line 8 swallows everything after it \u2014 because #(lf)<\/code> doesn’t terminate the comment. DataHub sees only one table.<\/p>\n\n\n\nWhy It’s Worse Than It Looks<\/h2>\n\n\n\n
--active<\/code> next to filter conditions, and disable WHERE clauses during development. This is normal SQL practice \u2014 but when Power BI encodes these queries with #(lf)<\/code>, every single --<\/code> comment becomes a lineage-destroying landmine.<\/p>\n\n\n\n--<\/code> comments perfectly when the newlines are real \u2014 the problem is entirely that #(lf)<\/code> is not decoded before parsing.<\/p>\n\n\n\nThe Fix: gsp-datahub-sidecar<\/h2>\n\n\n\n
\n
#(lf)<\/code>, #(cr)<\/code>, #(tab)<\/code>) back into real characters before any SQL parsing happens. This is the step that DataHub’s ingestion pipeline is missing.<\/li>\n#(lf)<\/code>):<\/p>\n\n\n\nselect#(lf)upper(cs.customercode) as customercode#(lf), cs.ear2id ...#(lf)from nast_truckload_domain.broker.dim_customer cs#(lf)--join ...#(lf)join ...#(lf)where cs.customerstatusid = 1 --active#(lf)and ...<\/code><\/pre>\n\n\n\n--join<\/code> on the encoded “line 8” swallows everything after it. Zero lineage extracted.<\/p>\n\n\n\n#(lf)<\/code> sequences become real newlines, and GSP correctly parses the result:<\/p>\n\n\n\n$ gsp-datahub-sidecar \\\n --sql-file powerbi_comments.sql \\\n --db-vendor dbvmssql \\\n --dry-run\n\nExtracted 2 table-level lineage relationships\n NAST_TRUCKLOAD_DOMAIN.BROKER.DIM_CUSTOMER --> DBO.CUSTOMER_BRANCHES (2 columns)\n NAST_CUSTOMER_DOMAIN.BROKER.REF_BRANCH --> DBO.CUSTOMER_BRANCHES (3 columns)\nColumn-level mappings extracted: 5<\/code><\/pre>\n\n\n\n\n
customercode<\/code> \u2190 dim_customer.customercode<\/code> (through UPPER()<\/code>)<\/li>\near2id<\/code> \u2190 dim_customer.ear2id<\/code><\/li>\nbranch_rollup_name<\/code> \u2190 ref_branch.branch_rollup_name<\/code><\/li>\nsubregion1<\/code> \u2190 ref_branch.subregion1<\/code><\/li>\nsubregion2<\/code> \u2190 ref_branch.subregion2<\/code><\/li>\n<\/ul>\n\n\n\nQuick Start<\/h2>\n\n\n\n
# 1. Install the sidecar\npip install git+https:\/\/github.com\/gudusoftware\/gsp-datahub-sidecar.git\n\n# 2. Verify with the built-in example\ngsp-datahub-sidecar \\\n --sql-file examples\/powerbi_comments.sql \\\n --db-vendor dbvmssql \\\n --dry-run\n\n# 3. Point at your DataHub instance\ngsp-datahub-sidecar \\\n --sql-file your_query.sql \\\n --db-vendor dbvmssql \\\n --datahub-url http:\/\/your-datahub:8080<\/code><\/pre>\n\n\n\nYour Power BI Connects to More Than One Database<\/h2>\n\n\n\n
#(lf)<\/code> encoding problem is Power BI specific, but the SQL inside your Power BI datasets comes from many different databases. One report might query SQL Server, another Snowflake, another Oracle. The sidecar handles all of them \u2014 GSP supports 20+ SQL dialects, so you just change the --db-vendor<\/code> flag to match your backend.<\/p>\n\n\n\nTOP<\/code>, Snowflake’s QUALIFY<\/code>, Oracle’s CONNECT BY<\/code> \u2014 and a parser that only handles one dialect will silently drop lineage from the others. The sidecar gives you correct lineage regardless of which database your Power BI report connects to.<\/p>\n\n\n\nPart of a Broader Pattern<\/h2>\n\n\n\n
#(lf)<\/code> comment issue is one example of a broader pattern: DataHub’s parser silently drops lineage on SQL constructs it can’t fully parse. We’ve documented similar gaps with BigQuery procedural SQL<\/a> (DECLARE, IF\/END IF, temp tables) and dbt deduplication macros<\/a> (array_agg with struct unpacking).<\/p>\n\n\n\n
\n\n\n\n