Need some help to why this query ends up with errors caused by a mixed field (Car engines) all look good in the query, but when loading errors. I try to keep the format as Text and succeeds, but still trouble
Power Query:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Tekst1", type text}, {"Overskrift", type text}, {"Price out", type text}, {"Pic2", type text}, {"Pic1", type text}, {"PartCode", type text}, {"YearNo", type text}, {"Engine", type text}, {"Model", type text}, {"Make", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"PartCode", "Overskrift", "Tekst1"}, {{"Count", each _, type table [Make=nullable text, Model=nullable text, Engine=nullable text, YearNo=nullable text, BHP=nullable number, PartCode=nullable text, Pic1=nullable text, Pic2=nullable text, Price out=nullable number, Overskrift=nullable text, Tekst1=nullable text]}}),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 100000, 1, Int64.Type),
#"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Index", "Handle"}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Renamed Columns", "Count", {"Make", "Model", "Engine", "YearNo", "BHP", "PartCode", "Pic1", "Pic2", "Price out", "Overskrift", "Tekst1"}, {"Make", "Model", "Engine", "YearNo", "BHP", "PartCode.1", "Pic1", "Pic2", "Price out", "Overskrift.1", "Tekst1.1"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Count",{"Overskrift.1", "Tekst1.1"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Make", "Model", "Engine", "YearNo", "BHP", "PartCode.1", "PartCode", "Overskrift", "Tekst1", "Pic1", "Pic2", "Price out", "Handle"}),
#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"PartCode.1"}),
#"Inserted Merged Column" = Table.AddColumn(#"Removed Columns1", "pic", each Text.Combine({[Pic1], [Pic2]}, ","), type text),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Inserted Merged Column", {{"pic", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "pic"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"pic", type text}}),
#"Removed Columns2" = Table.RemoveColumns(#"Changed Type1",{"Pic1", "Pic2"}),
#"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns2",{"Handle", "Overskrift", "PartCode", "Tekst1", "pic", "Price out", "Make", "Model", "Engine", "YearNo", "BHP"}),
#"Added Custom" = Table.AddColumn(#"Reordered Columns1", "Collection", each "EBC Bremser"),
#"Reordered Columns2" = Table.ReorderColumns(#"Added Custom",{"Handle", "Overskrift", "PartCode", "Tekst1", "pic", "Price out", "Collection", "Make", "Model", "Engine", "YearNo", "BHP"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Reordered Columns2", "Collection", "Collection - Copy"),
#"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Collection", "Collection - Copy.1"),
#"Added Custom1" = Table.AddColumn(#"Duplicated Column1", "Image Position", each ""),
#"Reordered Columns3" = Table.ReorderColumns(#"Added Custom1",{"Handle", "Overskrift", "PartCode", "Tekst1", "pic", "Image Position", "Price out", "Collection", "Make", "Model", "Engine", "YearNo", "BHP", "Collection - Copy", "Collection - Copy.1"}),
#"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns3",{{"Collection - Copy.1", "Forhandler"}, {"Collection - Copy", "Vendor"}, {"BHP", "HK"}, {"pic", "Image Src"}, {"Make", "Mærke"}, {"Engine", "Motor"}, {"YearNo", "Årgang"}}),
#"Reordered Columns4" = Table.ReorderColumns(#"Renamed Columns1",{"Handle", "Overskrift", "PartCode", "Tekst1", "Image Src", "Image Position", "Vendor", "Price out", "Collection", "Mærke", "Model", "Motor", "Årgang", "HK", "Forhandler"}),
#"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns4",{{"Tekst1", "Body (HTML)"}, {"PartCode", "Variant SKU"}, {"Overskrift", "Title"}, {"Forhandler", "category"}}),
#"Removed Duplicates" = Table.Distinct(#"Renamed Columns2")
in
#"Removed Duplicates"
Last edited by a moderator:
