I have a folder with multiple Excel files, each with some common leading columns, and then a varying (per file) number of columns that I need to unpivot; I've written the code for unpivoting properly in #Sample File (4)#, but it seems like that custom code I wrote is being skipped and it is simply returning the unchanged worksheet?
After:
#"Invoke Custom Function1" = Table.AddColumn(#"Renamed Columns", "Transform File (4)", each #"Transform File (4)"([Content]))
....executes and I click a cell in the appended column containing the resulting [Table] object, it's just the unmodified original worksheet!
I don't exactly understand how all these queries + (Sample file, Transform File, Parameter) objects all work together, I think I only kinda understand.
I will post my code:
-- QUERY:
let
Source = Folder.Files(DataPath),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Filtered Rows" = Table.SelectRows(#"Filtered Hidden Files1", each Text.Contains([Name], "BDIA Data")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Name"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Name", "Filename"}}),
#"Invoke Custom Function1" = Table.AddColumn(#"Renamed Columns", "Transform File (4)", each #"Transform File (4)"([Content])),
// todo: this needs to be changed to proper columns once I get the transform working again
#"Expanded Transform File (4)" = Table.ExpandTableColumn(#"Invoke Custom Function1", "Transform File (4)", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"})
in
#"Expanded Transform File (4)"
-- Parameter4 (Sample File (4))
#"Sample File (4)" meta [IsParameterQuery=true, BinaryIdentifier=#"Sample File (4)", Type="Binary", IsParameterQueryRequired=true]
-- function: Transform File(4)
let
Source = (Parameter4) => let
Source = Excel.Workbook(Parameter4, null, true),
// we want the SECOND tab, so: Item=Source{1}
#"xls" = Source{[Item=Source{1}[Item],Kind="Sheet"]}[Data]
in
#"xls"
in
Source
-- Transform Sample File(4)
let
Source = Excel.Workbook(Parameter4, null, true),
#"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "(2)")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Name", "WorksheetName"}}),
theTable = Table.SelectColumns(#"Renamed Columns",{"WorksheetName", "Data"}),
dynamicExpandedColumns = Table.ExpandTableColumn(theTable, "Data", List.Union(List.Transform(theTable[Data], each Table.ColumnNames(_)))),
#"Removed Top Rows" = Table.Skip(dynamicExpandedColumns,5),
#"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows",{"Column3"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
#"Filtered Rows1" = Table.SelectRows(#"Promoted Headers", each [Column3] <> null and [Column3] <> ""),
#"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows1",{{"Column2", "CBF ID"}, {"Column3", "CBF Description"}}),
removedWorksheetName = Table.RemoveColumns(#"Renamed Columns1",{Table.ColumnNames(#"Renamed Columns1"){0}}),
unpivotAppColumns = Table.UnpivotOtherColumns(removedWorksheetName, {"CBF ID", "CBF Description"}, "AppTechID", "Value")
in
unpivotAppColumns
The Transform Sample File(4) is rendering perfectly during design, but when I check the results when it's called from the query, it seems like it didn't get invoked.
Am I doing something obviously wrong?
@MarcelBeug
@ImkeF
UPDATE: After doing these loads over and over for different tabs, I'm becoming more familiar with how this generally works, but I still can't seem to figure out a good approach (of clicking through steps in order to end up with the function, transform, etc files the way I like, consistently receiving the Parameter as Excel.Workbook, etc). From watching a youtube video, I just learned instead of choosing a specific worksheet in the wizard, there's a secret hidden where you can actually right click the workbook in that tree and a menu appears allowing you to expand the worksheets into a table!
If anyone could recommend a GOOD and comprehensive blog post on how to structure these things, I would very much appreciate it.
