Starting with a generic excel workbook, I need to create a function that adds a column that contains a JSON representation of the row ("column 1 header":"column 1 value", "column 2 header":"column 2 value", etc). This needs to work for generic workbooks.
I'm almost there - as this is actually creating the column correctly, but 2 problems...
1. The "Convert to Text" line is needed in order to get text for the JSON string. But I don't want to actually change all the columns to type text. Is there a way to convert to text for the JSON string without actually changing the actual table?
2. I am stuck trying to make this a generic function I can call from the other queries I have.
let
Source = Folder.Files("C:\Users\bpeadmin\Desktop"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Rows", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Convert To Text" = Table.TransformColumnTypes(#"Expanded Table Column1",List.Transform(Table.ColumnNames(#"Expanded Table Column1"), each {_, Text.Type})),
#"Add JSON Column" = Table.AddColumn(#"Convert To Text", "JSONString", each(Text.FromBinary(Json.FromValue(#"Convert To Text"{_}))), Text.Type)
in
#"Add JSON Column"
Any tips/suggestions would be most appreciated!
