Forum Discussion
Most Valuable ProfessionalDo you need a parameter to combine files?
My simple question is; Do I really need this parameter?
My solution below seems to work fine without the parameter but I'm to novice at this to feel confident that I have not broken something in the process.
The solution
First I replaced the Parameter1 with the Sample File in the Function here:
Then I updated the Transform Sample File to go directly to the Sample File instead of the Parameter:
I get the same result as I get with the parameter but I've now removed it. The correct data comes from each of my files.
Context
The reason I want to dig into this is because I'm building a Power BI Template App that currently uses an Azure Blob storage with JSON files in it. I need to combine them into my query.
The issue is that Parameters of type "Any" or "Binary" is not allowed for Template apps.
I'm looking for validation that my solution will work and know about potential pit falls. What are the cons of removing the parameter like this? What's the risks?
Here's a decent explanation of how you can do this without the parameter nonsense:
https://www.dutchdatadude.com/loading-multiple-json-files-using-power-query/The basic outline is:
- Click on one of the binaries and define what transformations you want to do to it.
- Turn that transformation on a specific file into a function.
- Add a custom column to your original table calling that function and pass in [Content] and/or whatever columns form the original table you need.
- Expand the new custom column.
Here's an example using some random json config files I have on my computer:
let Source = Folder.Files("C:\Users\Alexis\Downloads"), #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".json") and Text.Contains([Name], "config ")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows", {"Content", "Name"}), #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Json.Document([Content])), #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"acq_function", "rounds", "draw_score"}) in #"Expanded Custom"In this simple case, I don't define a separate custom function. All I need is a custom column that applies Json.Document to the [Content] column.
2 Replies
- lbendlin
Super User
That construct is for beginners. Once you understand how it works, delete all of it and roll your own ingestion logic.
- AlexisOlson
Here's a decent explanation of how you can do this without the parameter nonsense:
https://www.dutchdatadude.com/loading-multiple-json-files-using-power-query/The basic outline is:
- Click on one of the binaries and define what transformations you want to do to it.
- Turn that transformation on a specific file into a function.
- Add a custom column to your original table calling that function and pass in [Content] and/or whatever columns form the original table you need.
- Expand the new custom column.
Here's an example using some random json config files I have on my computer:
let Source = Folder.Files("C:\Users\Alexis\Downloads"), #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".json") and Text.Contains([Name], "config ")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows", {"Content", "Name"}), #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Json.Document([Content])), #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"acq_function", "rounds", "draw_score"}) in #"Expanded Custom"In this simple case, I don't define a separate custom function. All I need is a custom column that applies Json.Document to the [Content] column.
