Forum Discussion
Helper IVCombine data and unpivot
Hello all,
(I am new to PowerBI)
I am trying to combine datasets from a folder.
Howerver, these datasets need to be unpivoted.
After importing my datasets, I have unpivoted the "transform sample file" .
Once that is done, I add a column in the main table with the "invoked custom function" from the query editor and choose my transformed file that has been unpivoted.
However, I get an error in my transform file column. See below.
Any idea how I can solve this? Am I following the correct steps?
When I click on the error, it says: "An error occurred in the ‘Transform File (5)’ query. Expression.Error: The column 'SK' of the table wasn't found." (It is referring from the name of the column in the original excel file. This column name was removed since the table was unpivoted )
Thanks for the help
Hi , Chrisjr
Hi, thanks for your patient explanation for your need , I update the Table.ReorderColumns() function in your M code , and make it dynamic to get the column names ,you can try to use this M code in the "Advanced Editor" to test.
let Source = Excel.Workbook(Folder.Files("C:\Users\admin\OneDrive - Contoso\zyy")[Content]{1}, null, true), Sheet1_Sheet = Source[Data]{0}, #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Reordered Columns" = Table.ReorderColumns(#"Promoted Headers", {Table.ColumnNames(#"Promoted Headers"){1}} & {Table.ColumnNames(#"Promoted Headers"){0}} & List.Skip(Table.ColumnNames(#"Promoted Headers"),2) ), #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each (Table.ColumnNames(#"Promoted Headers"){2} <> null and Table.ColumnNames(#"Promoted Headers"){2} <> 0) and ([Column1] <> null and [Column1] <> "Final Adjusted Index only with Pack" and [Column1] <> "Final Index adjusted" and [Column1] <> "Final Index adjusted without technical features" and [Column1] <> "Final Index adjustment only" and [Column1] <> "Final Index list price" and [Column1] <> "Final Promo Index" and [Column1] <> "Final Promo Index with Annual Taxes" and [Column1] <> "Final Promo Index with Consumption" and [Column1] <> "Final Promo Index with Ecobonus" and [Column1] <> "Final Promo Index with Financing" and [Column1] <> "Final Promo Index with Launch Taxes" and [Column1] <> "Final Promo Index with Packs & at choice 1" and [Column1] <> "Final Promo Index with Packs & at choice 2" and [Column1] <> "Final Promo Index with Stock Bonus" and [Column1] <> "Final Transaction Index W/ Packs & Annual Taxes" and [Column1] <> "Final Transaction Index W/ Packs & Consumption" and [Column1] <> "Final Transaction Index W/ Packs & Ecobonus" and [Column1] <> "Final Transaction Index W/ Packs & Financing" and [Column1] <> "Final Transaction Index W/ Packs & Launch Taxes" and [Column1] <> "Final Transaction Index W/ Packs & Stock Bonus" and [Column1] <> "Final Transaction Index with Packs & at choice 1" and [Column1] <> "Final Transaction Index with Packs & at choice 2" and [Column1] <> "Final Transaction Price Index")), #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each if try Value.Is(Number.From([#"Column1"]), type number) otherwise false then "CarVerson" else [#"Column1"]), #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom",List.InsertRange(Table.ColumnNames(#"Reordered Columns"),1,{"Custom"})), #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns1",{"Column1", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25"}), #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{Table.ColumnNames(#"Removed Columns"){0}, type text}}), #"Renamed Columns4" = Table.RenameColumns(#"Changed Type",{{Table.ColumnNames(#"Changed Type"){0}, "Column1"}}), #"Added Custom1" = Table.AddColumn(#"Renamed Columns4", "Custom.1", each try (if Number.FromText([Column1]) > 0 then null else null) otherwise [Column1]), #"Reordered Columns2" = Table.ReorderColumns(#"Added Custom1",{"Custom.1"} & List.RemoveLastN(Table.ColumnNames(#"Added Custom1"))), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns2",{{"Custom.1", "ReferenceCar"}}), #"Filled Down" = Table.FillDown(#"Renamed Columns",{"ReferenceCar"}), #"Removed Columns1" = Table.RemoveColumns(#"Filled Down",{"Column1"}), #"Added Index" = Table.AddIndexColumn(#"Removed Columns1", "Index", 0, 1, Int64.Type), Custom1 = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 24), Int64.Type}}), #"Renamed Columns1" = Table.RenameColumns(Custom1,{{"Custom", "Brand"}}), #"Trimmed Text" = Table.TransformColumns(#"Renamed Columns1",{{"Brand", Text.Trim, type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Trimmed Text", {"ReferenceCar", "Brand", "Index"}, "Attribute", "Value"), #"Added Index1" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index.1", 0, 1, Int64.Type), #"Added Conditional Column" = Table.AddColumn(#"Added Index1", "Custom", each if [Brand] = "CarVerson" then [Value] else null), #"Sorted Rows" = Table.Sort(#"Added Conditional Column",{{"Index", Order.Ascending}, {"Attribute", Order.Ascending}, {"Index.1", Order.Ascending}}), #"Filled Down1" = Table.FillDown(#"Sorted Rows",{"Custom"}), #"Filtered Rows1" = Table.SelectRows(#"Filled Down1", each ([Brand] <> "CarVerson")), #"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows1",{"Index"}), #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"Brand", "IndexType"}, {"Attribute", "BikeModel"}}), #"Reordered Columns3" = Table.ReorderColumns(#"Renamed Columns2",{"ReferenceCar", "IndexType", "BikeModel", "Custom", "Value", "Index.1"}), #"Renamed Columns3" = Table.RenameColumns(#"Reordered Columns3",{{"Custom", "BikeVersion"}}) in #"Renamed Columns3"Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
14 Replies
- v-yueyunzh-msft
Community Support
Hi , Chrisjr
According to your description, the [Transform File (5)] return an error code like this:
Expression.Error: The column 'SK' of the table wasn't found.
For this problem, I don't have your detailed steps and data, I can only give you the following solution:
(1)For this error, it can be seen that there is no [SK] column caused by the error. First you need to find out which step caused the error.
(2)Then for solutions without SK columns, you can try to use the Table.ColumnNames() function to dynamically get the column names you need to eliminate the occurrence of this error.
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
ChrisjrHi v-yueyunzh-msft thanks for your reply.
here is the link of a sample of my BI app.
https://drive.google.com/drive/folders/1lv-vcVN8JmAnFFJs85B9L1lMk1QrwJ90?usp=sharing
You will see when I try to invoke samplefile5, I get this error.
Thanks for your help.
- v-yueyunzh-msft
Hi, Chrisjr
Thanks for your quck response and your excel you provided. Can you share the M code "Advanced Editor" you now used because i'm not fully understand what the steps you want to get and the end data you want to show .
Or can you explain the detailed steps to combine the three excel and the end result in the end to us?
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
- Chrisjr
v-yueyunzh-msft thanks for the reply
see the link : https://drive.google.com/file/d/1FnDvSAl9Vu5f1ZdawJmymVa33K_VLKAC/view?usp=share_link
step: I invok the transform sample file 5 from the invoke custom column button.
Then I get an error in the new colun
Here is the m code:
let
Source = Folder.Files("C:\Users\Christian Jadoul\SynologyDrive\Skoda\PSK"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Date modified] > #datetime(2021, 12, 1, 0, 0, 0)),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each not Text.Contains([Name], "FLEET")),
#"Filtered Rows3" = Table.SelectRows(#"Filtered Rows2", each not Text.Contains([Folder Path], "PSK Factory")),
#"Filtered Rows4" = Table.SelectRows(#"Filtered Rows3", each not Text.Contains([Folder Path], "FLEET")),
#"Filtered Rows5" = Table.SelectRows(#"Filtered Rows4", each not Text.Contains([Folder Path], "Fleet")),
Custom1 = Table.SelectRows(#"Filtered Rows5", each [Attributes]?[Hidden]? <> true),
#"Filtered Hidden Files1" = Table.SelectRows(Custom1, each [Attributes]?[Hidden]? <> true),
#"Invoked Custom Function" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (5)", each #"Transform File (5)"([Content]))
in
#"Invoked Custom Function"And the M code after expanded the column:
let
Source = Folder.Files("C:\Users\Christian Jadoul\SynologyDrive\Skoda\PSK"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Date modified] > #datetime(2021, 12, 1, 0, 0, 0)),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each not Text.Contains([Name], "FLEET")),
#"Filtered Rows3" = Table.SelectRows(#"Filtered Rows2", each not Text.Contains([Folder Path], "PSK Factory")),
#"Filtered Rows4" = Table.SelectRows(#"Filtered Rows3", each not Text.Contains([Folder Path], "FLEET")),
#"Filtered Rows5" = Table.SelectRows(#"Filtered Rows4", each not Text.Contains([Folder Path], "Fleet")),
Custom1 = Table.SelectRows(#"Filtered Rows5", each [Attributes]?[Hidden]? <> true),
#"Filtered Hidden Files1" = Table.SelectRows(Custom1, each [Attributes]?[Hidden]? <> true),
#"Invoked Custom Function" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (5)", each #"Transform File (5)"([Content])),
#"Expanded Transform File (5)" = Table.ExpandTableColumn(#"Invoked Custom Function", "Transform File (5)", {"ReferenceCarVersion", "CarModel", "CarVersion", "IndexType", "Index.1", "Value"}, {"Transform File (5).ReferenceCarVersion", "Transform File (5).CarModel", "Transform File (5).CarVersion", "Transform File (5).IndexType", "Transform File (5).Index.1", "Transform File (5).Value"})
in
#"Expanded Transform File (5)"The source folder has many excel files with the same structure. However, the row 1 of each excel files can have different name.