Changing incorrect data in Power Query

Hi Giizzo ,

 

Select the [Company] column, go to the Transform tab and hit Replace Values. In the first box, type "Wrong", then in the second, type null. This will clear your incorrect entries.

Then sort your table first by [Name] ascending, then by [Date] ascending to ensure we get the chronological flow correct.

Then, select the [Company] column again, go to the Transform tab and hit Fill > Fill Down. This should fill in your gaps with the last chronological entry.

 

Pete

 

Hi Giizzo ,

You can try this query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZG9DsIwDIRfBWWu5PpMfjrzCkgMVQcGxESLgIW3J1JF05/EVRZL993ZjtvWnG/vD5vKoAZTfLE8DY/ntf8e2HTVChAKsby8hv6eE5s9N2NGIEvsZFhip2fYMaMwpCPYshoIQe/fkNTZ/vgTKHziBBxJ9ARHTk/w5PWEJnOnSWQs75AayOwOG7+k+RXRFraXtJzi9gs3tkBYA90P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, Company = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}, {"Company", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","Wrong",null,Replacer.ReplaceValue,{"Company"}),
    #"Grouped Rows" = 
        Table.Group(
            #"Replaced Value", {"Name"}, 
            {
                {
                    "Data", each 
                        if Table.First(_)[Company] = null then 
                        Table.FillUp(Table.FillDown(_,{"Company"}),{"Company"}) else
                        Table.FillDown(_,{"Company"})
                    , type table [Name=nullable text, Date=nullable date, Company=nullable text]
                }
            }
        ),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Date", "Company"}, {"Date", "Company"})
in
    #"Expanded Data"

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.