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
Super UserI think that should be possible. Please provide a small sample that covers the issue, and indicate the expected outcome based on that sample.
Some things to ponder: Table.ColumnNames() and List.Contains()
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.
- lbendlin
Nearly 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?