replace null values by previous recorded values with condinon per group

[Chrisjr]

Hello All , 

I have the below dataset.

Brand	Model Version	01/2021	01-02-21	03/2021	04/2021	01-05-21	06/2021	07/2021
Skoda, Enyaq (SUV, 5D)	IV 132kW	380	null	null	null	null	null	4000
Skoda, Enyaq (SUV, 5D)	IV 150kW	4400	null	4675	null	null	null	495
Kia, EV6 (SUV, 5D)	77KWH 168kW EV	null	null	null	5390	null	null	null
Kia, EV6 (SUV, 5D)	77KWH 168kW EV	null	null	null	590	null	null	null
Volvo, XC40 (SUV, 5D)	69KWH 175kW EV A R CORE	null	null	null	null	null	null	500
Volvo, XC40 (SUV, 5D)	69KWH 175kW EV A R CORE	null	null	null	null	null	null	6500
FORD, MUSTANG MACH-E (SUV, 5D)	76KWH 198kW EV A R RWD	480	null	180	null	null	180	null
FORD, MUSTANG MACH-E (SUV, 5D)	76KWH 198kW EV A R RWD	400	null	200	null	null	null	null
Renault, MEGANE E-TECH (Hatchback, 5D)	R130 96kW EV A TECHNO	null	null	null	null	null	null	null
Renault, MEGANE E-TECH (Hatchback, 5D)	R220 160kW EV A TECHNO	null	null	null	null	null	null	null
Mercedes-Benz, EqA (SUV, 5D)	67KWH 140kW EV A BUSINESS.	null	null	null	560	null	null	null
Mercedes-Benz, EqA (SUV, 5D)	67KWH 140kW EV A BUSINESS.	null	null	null	78900	null	null	null
Polestar, 2 (Sedan, 5D)	82KWH 220kW EV A R	null	null	300	null	null	null	null
Polestar, 2 (Sedan, 5D)	82KWH 220kW EV A R	null	null	2500	null	null	null	null
Audi, Q4 (SUV, 5D)	82KWH 150kW EV A R ATTRAC.	null	null	null	48500	null	null	null
Audi, Q4 (SUV, 5D)	90KWH 150kW	null	null	null	8900	null	9800	null
Audi, Q4 (SUV, 5D)	90KWH 150kW EV A R ATTRAC.	null	null	null	4800	null	null	null

The problem I am trying to achieve is the following: per brand, when the value is null for one model but not null for the other model(s) of the same brand at the same date, I need PBI to go fetch the value of the previous month or the latest value  recorded if the one of the previous month(s) is null.

For example, for the first brand Skoda, Enyaq (SUV, 5D), on the 03/2021, one model has a null value and the other one has a value.

Therefore, for the model version IV 132kW , on the 03/2021, i need to replace the null value with the latest record , which is 380. 

The end result would be as below:

Brand	Model Version	01/2021	01-02-21	03/2021	04/2021	01-05-21	06/2021	07/2021
Skoda, Enyaq (SUV, 5D)	IV 132kW	380	null	380	null	null	null	4000
Skoda, Enyaq (SUV, 5D)	IV 150kW	4400	null	4675	null	null	null	495
Kia, EV6 (SUV, 5D)	77KWH 168kW EV	null	null	null	5390	null	null	null
Kia, EV6 (SUV, 5D)	77KWH 168kW EV	null	null	null	590	null	null	null
Volvo, XC40 (SUV, 5D)	69KWH 175kW EV A R CORE	null	null	null	null	null	null	500
Volvo, XC40 (SUV, 5D)	69KWH 175kW EV A R CORE	null	null	null	null	null	null	6500
FORD, MUSTANG MACH-E (SUV, 5D)	76KWH 198kW EV A R RWD	480	null	180	null	null	180	null
FORD, MUSTANG MACH-E (SUV, 5D)	76KWH 198kW EV A R RWD	400	null	200	null	null	200	null
Renault, MEGANE E-TECH (Hatchback, 5D)	R130 96kW EV A TECHNO	null	null	null	null	null	null	null
Renault, MEGANE E-TECH (Hatchback, 5D)	R220 160kW EV A TECHNO	null	null	null	null	null	null	null
Mercedes-Benz, EqA (SUV, 5D)	67KWH 140kW EV A BUSINESS.	null	null	null	560	null	null	null
Mercedes-Benz, EqA (SUV, 5D)	67KWH 140kW EV A BUSINESS.	null	null	null	78900	null	null	null
Polestar, 2 (Sedan, 5D)	82KWH 220kW EV A R	null	null	300	null	null	null	null
Polestar, 2 (Sedan, 5D)	82KWH 220kW EV A R	null	null	2500	null	null	null	null
Audi, Q4 (SUV, 5D)	82KWH 150kW EV A R ATTRAC.	null	null	null	48500	null	48500	null
Audi, Q4 (SUV, 5D)	90KWH 150kW	null	null	null	8900	null	9800	null
Audi, Q4 (SUV, 5D)	90KWH 150kW EV A R ATTRAC.	null	null	null	4800	null	4800	null

Anyone woud have an idea on how to achieve this ? 

Thank you

[Chrisjr]

Hi @ronrsnfld and @ichavarria Applologies for the late answer, forgot to mark it as solution. It worked @ronrsnfld 🙏 thanks a lot

[ronrsnfld · Accepted solution]

Try pasting the below into a blank query

See the comments for the algorithm.

There may be more efficient methods, but this seems to work

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZRbb4IwGIb/yheutgSXcir0smInxogbIC4xXjAhmZFAPC3Zfv2KiJNsVZzuhpKmPE/79g2TieQv8jiSgWUf0RLu/FEog9G5l2SpF4KiqYsx8HfNQvyZbdP0zKAjhKSpfAZroBKr8+VH32LTEHKJscP25wU0xDWkafbHDijY4lQWgohhaERwiqvJp8Bhnr7nMrzYOqqxMdmxTaNkU/DAHnqsWc6VeJ/2fzpwJXkceh0ZBiM/oG4XBtR2WqyeFt7ZiPVt88ad4vaO66P80qWjuStNx4VS0enWFiovyaJtuuE21qUuA9YKmO3AnRNtZm+v0WxRKT1FQ0BwJSxWucOLgvyDU1URbx+6hXSQrGZJnKxb7ST75E1f0npTyqbrB1d75Pdc5vsPwu7hE6W/uc20iOg2C99TnibrTbSSQeWiJI6ySmWphYoHeajKD4rWoCdXCVSjgYFu47kMz3otqBJe/jD3RadB4FFbGJRuCWVCC0EHiwhbj59YCF1Cbbx34danXw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Brand = _t, #"Model Version" = _t, #"01/2021" = _t, #"01-02-21" = _t, #"03/2021" = _t, #"04/2021" = _t, #"01-05-21" = _t, #"06/2021" = _t, #"07/2021" = _t]),

//Next two lines are because some of the nulls in your copied data were text strings and not actual nulls
//could be different depending on your real data
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Brand", type text}, {"Model Version", type text}, {"01/2021", Int64.Type}, {"01-02-21", type text}, {"03/2021", Int64.Type}, {"04/2021", Int64.Type}, {"01-05-21", type text}, {"06/2021", Int64.Type}, {"07/2021", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","null",null,Replacer.ReplaceValue,Table.ColumnNames(#"Changed Type")),

//Group by Brand
//For each Brand
/*  Transpose the data
    Duplicate the columns of data
    Fill down the duplicated columns
    Create a list of the column names for the Data fields (the original data) and the Fill Fields (the duplicated columns)
    For each row,
        If there are any non-nulls, then replace the Data fields with the Fill Fields and rename back to the Data Field names
        Else don't change anything (leave them all nulls)
    Then add back the first column of data (which was the original headers)
        Transpose back
        Expand the table
        Reset the data types
*/
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Brand"}, {
        {"Normalize", (t)=> let 
            transpose = Table.Transpose(Table.DemoteHeaders(t)),
            #"Data Fields" = List.RemoveFirstN(Table.ColumnNames(transpose),1),
            #"Duplicate Columns" = List.Accumulate(#"Data Fields", transpose,
                    (state, current)=> Table.DuplicateColumn(state, current, current & "- Copy")),
            #"Fill Down" = Table.FillDown(#"Duplicate Columns",
                    List.Select(Table.ColumnNames(#"Duplicate Columns"), each Text.EndsWith(_, "- Copy"))),
            #"Fill Fields" = List.LastN(Table.ColumnNames(#"Fill Down"), List.Count(#"Data Fields")),
            #"Transform Columns by Row Contents" = 
                Table.FromRecords(
                    Table.TransformRows(#"Fill Down",
                        (r) => if List.NonNullCount(Record.FieldValues(Record.SelectFields(r,#"Data Fields"))) > 0
                                then Record.RenameFields(Record.SelectFields(r,#"Fill Fields"), List.Zip({#"Fill Fields",#"Data Fields"}))
                                else Record.SelectFields(r,#"Data Fields"))),

            #"Add Back Column 1" = 
                Table.FromColumns(
                    {transpose[Column1]} &
                    Table.ToColumns(#"Transform Columns by Row Contents")),
                    
            #"Transpose Back" = Table.Transpose(#"Add Back Column 1"),
            #"Promote Headers" = Table.PromoteHeaders(#"Transpose Back")
        in 
            #"Promote Headers"
          }}),
    #"Expanded Normalize" = Table.ExpandTableColumn(#"Grouped Rows", "Normalize", List.RemoveFirstN(Table.ColumnNames(#"Grouped Rows"[Normalize]{0}))),
    #"Set Data Types" = Table.TransformColumnTypes(#"Expanded Normalize", 
        List.Zip({Table.ColumnNames(#"Expanded Normalize"),{type text, type text, List.Repeat({type nullable number}, Table.ColumnCount(#"Expanded Normalize")-2)}}))
in
    #"Expanded Normalize"

Results from your data posted above in Power Query

In Power BI Desktop

[ichavarria]

You might use a combination of Power Query and DAX in Power BI. Here's a step-by-step guide:

1. Import your data into Power BI and create a new query by selecting "Edit Queries" from the "Home" tab.

2. In the new query window, select the "Transform Data" tab and then click on "Group By".

3. In the "Group By" dialog, set the "Group By" column to "Brand" and the "Aggregate" column to "All Rows".

4. Click on "OK" to create a new table that groups the data by brand.

5. Right-click on the new table and select "Duplicate".

6. Rename the duplicated table to "Previous Month".

7. Click on "Add Column" and then "Custom Column".

8. In the "Custom Column" dialog, enter the following formula:

= Table.SelectRows(#"Your Original Table", each [Brand] = [Brand] and [Model Version] <> [Model Version] and [Date] = Date.AddMonths([Date], -1))

9. Click on "OK" to create a new column that filters the original table to find the previous month's data for the same brand and a different model version.

10. Click on "Expand" in the new column to extract the relevant columns from the filtered table.

11. Rename the expanded columns to include the prefix "Prev" (e.g., "Prev 01/2021", "Prev 03/2021", etc.).

12. Merge the "Previous Month" table with the original table using the "Brand" and "Date" columns as the join keys.

13. Add a new column to the merged table that checks if the value for a given model version is null and, if so, replaces it with the value from the previous month (or the latest value if the previous month is also null).

14. Use the following formula in the new column:

= if [01/2021] <> null then [01/2021] else if [Prev 01/2021] <> null then [Prev 01/2021] else if [03/2021] <> null then [03/2021] else if [Prev 03/2021] <> null then [Prev 03/2021] else if [04/2021] <> null then [04/2021] else if [Prev 04/2021] <> null then [Prev 04/2021] else if [01-05-21] <> null then [01-05-21] else if [Prev 01-05-21] <> null then [Prev 01-05-21] else if [06/2021] <> null then [06/2021] else if [Prev 06/2021] <> null then [Prev 06/2021] else [07/2021]

15. Repeat step 14 for each month column in your table.

16. Remove any unnecessary columns and rename the new column as desired.

17. Close and apply the changes to the query.

Your final table should now have the null values replaced with the previous month's values (or the latest value if the previous month is also null) for each brand/model/version combination.

Best regards, 

Isaac Chavarria 


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly