Forum Discussion
How can I split a variable-depth, slash-delimited path_column field into separate col in KQL?
Sure thing - just remove the column defintion fret the pivot function. Then the previous query will be dynamic
Hi
I need some help to understand what you are trying to accomplish.
You are asking for a dynamic resultset (dynamic number of columns) based on the hierarchy column. And in the same time you don't want a variable number of columns due to fixed meta-data in Power BI.
Perhaps I'm misunderstanding your question - please help 🙂
With that said, I can help you create 100 columns in the hierarchy, so you have enough columns almost no matter how deep the hierarchy becomes in the future.
Then you will have a fixed number of columns for Power Query and can go from there.
Let me know what you think of above approach and if you can help me understand what you are asking 🙂
Cheers
Brian
ok, let me clarify first the ETL part then we will go to BI part
We’re ingesting live data from Event Hub into an Eventhouse kql table. One of the columns contains values separated by '/', like:
047/Pass/fail/assure
The number of segments (separated by '/') is not fixed — it changes over time. I'd like to split this column dynamically into multiple columns (Col_1, Col_2, etc.) based on the number of values, and have the schema auto-adjust if more segments appear in future records. For example:
- Row with 4 segments → Col_1 to Col_4
- Row with 6 segments → auto-extend schema to Col_6, with missing values as null for shorter rows
Can Eventhouse/KQL support dynamic schema creation or updates like this during ingestion?
Or is there a best practice for handling this kind of variable structure using something else?
