Changing incorrect data in Power Query

[Giizzo]

Hi all,

A data file which we process in Power BI has incorrect data and we want to fix this in Power Query. We have a list with three columns, namely 'Employee Name', 'Date' and 'Company Name'.

An employee can appear multiple times with various dates and/or company names. Some of the company names are wrong; in this example they are called 'Wrong'.

Is it possible to change the company names indicated as 'Wrong' to the company name where the employee was listed prior to the date 'Wrong'; and if so, how?

Thanks in advance for any help!

[BA_Pete · Accepted solution]

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

[v-yingjl · Accepted solution]

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.

[Giizzo]

Thanks for the response. The solution offered by BA_Pete worked for me, but your response would no doubt work just as well or perhaps even better for a more complex table.

[ronrsnfld]

What about the case where the company name is Wrong on the first date the employee worked?

[Jakinta]

Group by Name,

FillDown [Company] within group,

then FillUp [Company] within group,

Expand.

[ronrsnfld]

That works to fill in the blanks, but it doesn't satisfy the condition set out by @Giizzo  where he specified: the company name where the employee was listed prior to the date 'Wrong';  So I was asking for clarification as to what, exactly, he wanted to do where there was no prior date.

[BA_Pete · Accepted solution]

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

[Giizzo]

Thanks for the response. This worked perfectly.