Forum Discussion
Solved
Clean database in Power Query (remove cell content according to rules)
Hello, I’ve a consolidated database (consolidation of weekly reports) to track challenges on weekly basis. Looks: Report Initiation Date Initiator Topic Issue Description Suppli...
You are not indicating which week entry you want to keep. Here's a version that keeps the first entry:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("3ZdbT9swFMe/ylGfNqlCacqlL3soaJOYBmxs0h6AB9c+LV5dO/OFqN9+56SXNGigDIFGeEja2mn9v/waOVdXvTIbQJ7lw16/9xOVxaDEsg+fhU3CL2G/v5n8KpKhl8szPqEC6YzzYF0EcSe0ERODNDPmg0+DPRiHOYRUFEajBzFxKYJKCEpEvPbXNt+DUzt1fgEyhegW6Hl0uAdfnJsDTYAwEb0VUd9hqBWUGcu5KLzt3fTb6z/T8lYg/8DH30kXC7Rx9z1MvJujJUMQCuER6NS0dszH8crahVfkqb5w7eeHFzZMaSY6yK0Coy2ulp5Xwvcr4bgRnncz+H/U37HgB4NauYgs9VsSRsclB+GdSjJW4UtXZUijJ3ycrPSdWoos6hlFzXW5yTa39dLDl+j8HEtY0JJeCwPOmmV9GZTZiEM6WVcdQFe6UfHouC4agkteIiTLCYsQMIR1V9v+D5oxtvPyCvt/TPhT+x/szHv8hTKiImGu0JJVTRCkcQHVFgv+1mqkhajRVtQxlWw5QIt+xqJObUjTqZaawyxcSSl8uE5ZNqTu0ShtZ6ESHKJ3dgZoXZrdbmXnzFBV9uZqIkneUn5AtdkZAt8PlNJRO0t0rRao7i7LHTXlIG9GvP+GOG/n5fk4P3cgRSEkQUcuK6Dp07yietttFRKZMYmbYZPf6591HskdBfpOoSHTJLREnEM2er/7txi199k9Ag+6TODhU7x0n8DHfHaPwMP/ROCDrOlFYZBREOxkl7ejpyjvPm+P+XwbvH3Cia/sjB7e1lyeAT8juCn1hDLs0rbab1liruJzw8v9lY9eD+lbxuvtlxS2sf9qUN/Yg7Xz8Xzc34N4jS5Fg8Lz0xs9UISQ2Ju5e3A72YC7tZ3u4f03Ny+O980f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Report = _t, #"Initiation Date" = _t, Initiator = _t, Topic = _t, #"Issue Description" = _t, Supplier = _t, #"Product / SKU" = _t, Action = _t, Owner = _t, #"Due date" = _t, Status = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Report", type text}, {"Initiation Date", type date}, {"Initiator", type text}, {"Topic", type text}, {"Issue Description", type text}, {"Supplier", type text}, {"Product / SKU", type text}, {"Action", type text}, {"Owner", type text}, {"Due date", type text}, {"Status", type text}}), #"Removed Duplicates" = Table.Distinct(#"Changed Type", {"Initiation Date", "Initiator", "Topic", "Issue Description", "Supplier", "Product / SKU", "Action", "Owner", "Due date", "Status"}) in #"Removed Duplicates"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
You are not indicating which week entry you want to keep. Here's a version that keeps the first entry:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("3ZdbT9swFMe/ylGfNqlCacqlL3soaJOYBmxs0h6AB9c+LV5dO/OFqN9+56SXNGigDIFGeEja2mn9v/waOVdXvTIbQJ7lw16/9xOVxaDEsg+fhU3CL2G/v5n8KpKhl8szPqEC6YzzYF0EcSe0ERODNDPmg0+DPRiHOYRUFEajBzFxKYJKCEpEvPbXNt+DUzt1fgEyhegW6Hl0uAdfnJsDTYAwEb0VUd9hqBWUGcu5KLzt3fTb6z/T8lYg/8DH30kXC7Rx9z1MvJujJUMQCuER6NS0dszH8crahVfkqb5w7eeHFzZMaSY6yK0Coy2ulp5Xwvcr4bgRnncz+H/U37HgB4NauYgs9VsSRsclB+GdSjJW4UtXZUijJ3ycrPSdWoos6hlFzXW5yTa39dLDl+j8HEtY0JJeCwPOmmV9GZTZiEM6WVcdQFe6UfHouC4agkteIiTLCYsQMIR1V9v+D5oxtvPyCvt/TPhT+x/szHv8hTKiImGu0JJVTRCkcQHVFgv+1mqkhajRVtQxlWw5QIt+xqJObUjTqZaawyxcSSl8uE5ZNqTu0ShtZ6ESHKJ3dgZoXZrdbmXnzFBV9uZqIkneUn5AtdkZAt8PlNJRO0t0rRao7i7LHTXlIG9GvP+GOG/n5fk4P3cgRSEkQUcuK6Dp07yietttFRKZMYmbYZPf6591HskdBfpOoSHTJLREnEM2er/7txi199k9Ag+6TODhU7x0n8DHfHaPwMP/ROCDrOlFYZBREOxkl7ejpyjvPm+P+XwbvH3Cia/sjB7e1lyeAT8juCn1hDLs0rbab1liruJzw8v9lY9eD+lbxuvtlxS2sf9qUN/Yg7Xz8Xzc34N4jS5Fg8Lz0xs9UISQ2Ju5e3A72YC7tZ3u4f03Ny+O980f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Report = _t, #"Initiation Date" = _t, Initiator = _t, Topic = _t, #"Issue Description" = _t, Supplier = _t, #"Product / SKU" = _t, Action = _t, Owner = _t, #"Due date" = _t, Status = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Report", type text}, {"Initiation Date", type date}, {"Initiator", type text}, {"Topic", type text}, {"Issue Description", type text}, {"Supplier", type text}, {"Product / SKU", type text}, {"Action", type text}, {"Owner", type text}, {"Due date", type text}, {"Status", type text}}),
#"Removed Duplicates" = Table.Distinct(#"Changed Type", {"Initiation Date", "Initiator", "Topic", "Issue Description", "Supplier", "Product / SKU", "Action", "Owner", "Due date", "Status"})
in
#"Removed Duplicates"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
This is doing perfectly the job.
Thank you very much
Fab
