Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Below is the M-Code in Power Query for removing columns (this is from a model that I am using). Is it possible to arrange the list alphabetically for ease of future reference?
Table.RemoveColumns(dbo_tblPODetail,{"StockItem_ID","VatRateID", "VatRatePerc", "VatValue", "PriceIncl", "TotalVAT", "TotalIncl", "Category_ID", "Category_Code", "Category", "Subcategory_ID", "Subcategory_Code", "Subcategory1_ID", "Subcategory1_Code", "Variety_ID", "Variety_Code", "Packing_ID", "Packing_Code", "Size_ID", "Size_Code", "Grade_ID", "Grade_Code", "ProdAllocationID", "ProdAllocationCode","PriceExclDiscounted", "UOM_ID", "Supplier_Group_ID", "Supplier_Group_Code", "Supplier_Group", "Supplier_Group_Price_ID", "Supplier_Group_Price_Period_Start", "Supplier_Group_Price_Period_End", "Price_Per_Outer_Pack", "TotalCalculation", "Costing_Type", "Price_Per_kg","Subcategory", "Subcategory1", "Variety", "Packing", "Size", "Grade", "PackSize","Std_Wgt_Per_Outer_Pack", "Price_Week","Origin_Doc_Line_ID"})
Solved! Go to Solution.
I am not sure if there is a slicker way, but you can create a blank query and use List.Sort and Text.Combine like below
let
Source = Text.Combine(
List.Sort(
{"StockItem_ID","VatRateID", "VatRatePerc", "VatValue", "PriceIncl", "TotalVAT", "TotalIncl", "Category_ID", "Category_Code", "Category", "Subcategory_ID", "Subcategory_Code", "Subcategory1_ID", "Subcategory1_Code", "Variety_ID", "Variety_Code", "Packing_ID", "Packing_Code", "Size_ID", "Size_Code", "Grade_ID", "Grade_Code", "ProdAllocationID", "ProdAllocationCode","PriceExclDiscounted", "UOM_ID", "Supplier_Group_ID", "Supplier_Group_Code", "Supplier_Group", "Supplier_Group_Price_ID", "Supplier_Group_Price_Period_Start", "Supplier_Group_Price_Period_End", "Price_Per_Outer_Pack", "TotalCalculation", "Costing_Type", "Price_Per_kg","Subcategory", "Subcategory1", "Variety", "Packing", "Size", "Grade", "PackSize","Std_Wgt_Per_Outer_Pack", "Price_Week","Origin_Doc_Line_ID"}
),
", "
)
in
Source
Then copy and paste the output into your query.
I am not sure if there is a slicker way, but you can create a blank query and use List.Sort and Text.Combine like below
let
Source = Text.Combine(
List.Sort(
{"StockItem_ID","VatRateID", "VatRatePerc", "VatValue", "PriceIncl", "TotalVAT", "TotalIncl", "Category_ID", "Category_Code", "Category", "Subcategory_ID", "Subcategory_Code", "Subcategory1_ID", "Subcategory1_Code", "Variety_ID", "Variety_Code", "Packing_ID", "Packing_Code", "Size_ID", "Size_Code", "Grade_ID", "Grade_Code", "ProdAllocationID", "ProdAllocationCode","PriceExclDiscounted", "UOM_ID", "Supplier_Group_ID", "Supplier_Group_Code", "Supplier_Group", "Supplier_Group_Price_ID", "Supplier_Group_Price_Period_Start", "Supplier_Group_Price_Period_End", "Price_Per_Outer_Pack", "TotalCalculation", "Costing_Type", "Price_Per_kg","Subcategory", "Subcategory1", "Variety", "Packing", "Size", "Grade", "PackSize","Std_Wgt_Per_Outer_Pack", "Price_Week","Origin_Doc_Line_ID"}
),
", "
)
in
Source
Then copy and paste the output into your query.
Thank you!
Hi Greg, thanks. Not sure if I missed it, but how do you do the alphabetic sorting?
@charl_cfm Here you go:
"Category","Category_Code","Category_ID","Costing_Type","Grade","Grade_Code","Grade_ID","PackSize","Packing","Packing_Code","Packing_ID","PriceIncl","Price_Per_Outer_Pack","Price_Per_kg","Price_Week","ProdAllocationCode","ProdAllocationID","Size","Size_Code","Size_ID","Subcategory1","Subcategory1_Code","Subcategory1_ID","Subcategory_Code","Subcategory_ID","Supplier_Group","Supplier_Group_Code","Supplier_Group_ID","Supplier_Group_Price_ID","Supplier_Group_Price_Period_End","Supplier_Group_Price_Period_Start","TotalCalculation","TotalIncl","TotalVAT","UOM_ID","Variety","Variety_Code","Variety_ID","VatRatePerc","VatValue","Origin_Doc_Line_ID","PriceExclDiscounted","Std_Wgt_Per_Outer_Pack","StockItem_ID","Subcategory","VatRateID"