Forum Discussion
M Code to remove blanks and nulls from rows in a single sweep
Here are all the transforms, including the changed types and column names. This replaces the entirety of your function (minus the removal of duplicates, but that's easy to add).
h/t to Mr. von Neumann
I have created a small sample file, but I'm failing the IQ test on how to upload a spreadsheet to this forum. Try this link to pull a sample data file and the spreadsheet to run the query functions.
Based on the settings in the "Remove Empty" column I want fnSetMetaDataFromTable to remove both rows 4 and 6.
Super UserNearly there - can you please make the OneDrive link not ask for authentication?
Give it another shot...
- lbendlin
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZXbTuMwEIZfJeJqV4JkPD5fllIOWqAIKrTAchHaqHS3Tao0ef/1OKmbVGwXCSlNx649h/+zpy8vR2zAANnR8dHgdOjeUrrX3XtarlJn6NiPkQGE6WlWV4tputy4mdOrC/dmzEoOgGR2PjxhkCAw7WyRyK0pY8XJJ4IAsLsJ2Jmj80nrA/xeu93rvWLw2rVp9W1WRRxCLffZvF6mZfRY1NP3rHQz43WWUyHFpspmNM6XizxzhmUKGAdF1d6klVtOPw/HN0B+h5fDH5Qds5Z7Qdhe8PC0IUma4s29fz49k3fJDZXMjdaGxaiOXo//ozxDLmLFPiO+lmAs3xOfYUIJMtnasB2glFvHbgIY9Ob6oz4HliA5UU2lzqnpBNjZX+KglQUjrdCHOAiJVii1qzQE/4DDeeldNCAk83UxhcJJFmvWgHBaS3I3PBv5HHzwzR9/SmMkxxI8hHHl8o9ushkxiH7V7tCr6KEu59FVla12UBA4NxbNHhRsFNTeNOHkAotFQ9o4ef01Uk1Yiskbs2GxncSEYefwtbB13/SSEAsPeXR29SkWzToNSqBCqfEQCcUtShGq4wfuw6CuisDBWM9BoQIbw78pPKyLfE7ngtm4xUD5nC0262KTvi0zYpFcp2/RXVnM6mlFIUc348mogSCNkf5mdK+HEz40I5uI0JeMjYVHrpzpixLxVm9HaAfBywn93Y3fwLRrfw2DRiu1VeogBg0Wm7uwF/sDEAECcw3P+k3IUMVa7zDQ1vOLy4ChStdLnyXXGBtPwjUdyjKfFats+p7mdCP21LfW+EXbtuA/rhExys7fDdamSgMlZOMahVDMW2EKuoO9rgQJDw7bANgNoMPgSxwsWA3SHmxM2t16zjuVYiexfQ5Dpyi1mOengT/g7qz6QCBM70rssZjUb4t8TqrGBJsLpAXD1HWmyvWmtieFVT0ekovtZe3yUJ0sofk6abz7gx5LDKqfhLPvpJWdfbr5k7YthJ7U49tragCz3/WmWmV5tfHVFxsvYRD6PqvqMo8mRfSYuVNVRt8e6vV6ucjK7xQS0LVgJjSPTiIAzYUCcT95pDhFFQ1o5ZQ6wkdUaG1bbz/Pw0zoeX39Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Dept ID" = _t, #"Supplier Name" = _t, #"Supplier ID" = _t, #"Line Description" = _t, #"Sum of monetary amount" = _t, Account = _t, #"Account Description" = _t, #"Business Unit" = _t, #"PO Number" = _t, #"Line Number" = _t, #"Schedule Number" = _t, #"PO Distribution Line Number" = _t, #"Invoice Date" = _t, #"Payment Date" = _t, #"Payment Amount" = _t, #"Payment ID" = _t, #"Merchandise Amt" = _t, #"Sum Freight" = _t, #"Unit Price" = _t, #"Payment Method" = _t, Quantity = _t, #"Discount Amount" = _t, #"Due Date" = _t, #"Discount Due Date" = _t, #"Voucher Entered Date" = _t, #"Matched Date" = _t, #"Payment Terms ID" = _t, #"Payment Terms Description" = _t, Origin = _t, #"Voucher Style" = _t, #"Close Status" = _t, #"Post Status" = _t, #"Voucher Source" = _t, #"Invoice Number" = _t, #"Match Status" = _t, #"Bank Code" = _t, #"Bank Account" = _t, #"Voucher ID" = _t, #"Voucher Line Number" = _t, #"Accounting Date" = _t, #"Match Line Status" = _t, #"Voucher Approval Status" = _t, #"Voucher Approval Date" = _t, #"Supplier Persistence" = _t, #"Entered By" = _t, #"Last User to Update" = _t, #"Check Number" = _t, #"Check Amount" = _t]), #"Filtered Rows" = Table.SelectRows(Source, each not List.Contains({"",null},[Entered By]) and not List.Contains({"",null},[Check Number])) in #"Filtered Rows"Do you need help with the type changes and column renames too?
I guess I do need help understanding your approach.
It appears you have hard-coded the column names and the logic. I don't control the extract - it goes to multiple departments and IT makes changes based on inputs from those departments as well as Finance. As stated in my initial request I don't want to hard-code fields or edit the query itself because Power Query will run the multi-hour query as soon as I finish editing today, and the changes won't be in the file until tomorrow morning. The point of the function is to be able to enter field changes without having to edit the query itself, have it run at 6am the next morning, and correctly manipulate the fields while I'm driving into work. You'll see that the fnSetMetaDataFromTable function has no hard-coded fields or logic. I want the process parameterized via the function.