Integrating Dataflows Gen2 into Pipelines

Here is a solution which includes a simple notebook

I know you didn't want notebook, however this is what I came up with...

I'm asking ChatGPT to help me with the code so it gets a lot easier.

My variable is similar like yours:

My Notebook activity:

My Notebook looks like this:

I have connected my Notebook to a default Lakehouse. This is indicated by the pin symbol.

This is called "attach a default Lakehouse" and you do it easily in the user interface. You see the pin symbol then it's good. You can also do it without a "default Lakehouse" but then you need to add some more code.

ChatGPT helped me with the code. 

This creates a file. I just called it output.json. Every time I run the pipeline, the file content gets overwritten.

My DataflowGen2 connects to this output.json file:

let
  Source = Lakehouse.Contents([]),
  #"Navigation 1" = Source{[workspaceId = "<EnterWorkspaceIdHere>"]}[Data],
  #"Navigation 2" = #"Navigation 1"{[lakehouseId = "<EnterLakehouseIdHere>"]}[Data],
  #"Navigation 3" = #"Navigation 2"{[Id = "Files", ItemKind = "Folder"]}[Data],
  #"Navigation 4" = #"Navigation 3"{[Name = "output.json"]}[Content],
  ToJson = Json.Document(#"Navigation 4"),
  #"Converted to table" = Record.ToTable(ToJson),
  #"Filtered rows" = Table.SelectRows(#"Converted to table", each ([Name] = "value")),
  #"Removed other columns" = Table.SelectColumns(#"Filtered rows", {"Value"}),
  #"Split column by delimiter" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(#"Removed other columns", {{"Value", type text}}), {{"Value", Splitter.SplitTextByDelimiter("|"), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value")
in
  #"Split column by delimiter"

I think here are some workarounds if you still don't want to use notebook. The post titles say Web activity, but the same principles should apply even if you're not using Web activity as your source.

Solved: How to save output from a web activity? - Microsoft Fabric Community 

How to save complete web activity output to ADLS Gen2? - Microsoft Q&A

29. Hands-On : Microsoft Fabric : Write Web Activity Json Output to Lakehouse |Set Variable Activity (youtube.com)

Thank you so much for your reply. As you guessed, I really am trying to avoid Notebooks. Therefore, I have come up with a simpler solution, but I am still running into trouble. I will make a new post to better describe it. Your expertise is very much appreciated, so please keep a look out for it! 🙂