Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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.
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?
Hi @TeeGee ,
Can you please share some sample data to help us clarify your table data structure to help use calcify your scenario? It is hard to direct test on m query formula without any sample data.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |