Hello everyone, I'm working with some sales data and I wanted to estimate the number of days an item remained at the same price. What I have at this point is a table with the sku's names in the columns, the dates as row's ID, and the price as values. Something like this:Date SKU 1SKU 2SKU3SKU 410/10/21null75548510/11/2125null54null10/12/21nullnullnull8710/13/21null71nullnull10/14/2125null54null10/15/21nullnullnull8710/16/2121nullnull8510/17/21null71nullnull I only want to fill down where the values between the null cells are the same, so the table should look like this: Date SKU 1SKU 2SKU3SKU 410/10/21null75548510/11/2125null54null10/12/2125null548710/13/212571548710/14/212571548710/15/21null71null8710/16/212171null8510/17/21null71nullnull Is there any way to do it? I would really appreciate the help 🙂

Anonymous

Not applicable

4 years ago

Solved

Fill Down only when the limit values are the same

Hello everyone,

I'm working with some sales data and I wanted to estimate the number of days an item remained at the same price. What I have at this point is a table with the sku's names in the columns, the dates as row's ID, and the price as values. Something like this:

Date	SKU 1	SKU 2	SKU3	SKU 4
10/10/21	null	75	54	85
10/11/21	25	null	54	null
10/12/21	null	null	null	87
10/13/21	null	71	null	null
10/14/21	25	null	54	null
10/15/21	null	null	null	87
10/16/21	21	null	null	85
10/17/21	null	71	null	null

I only want to fill down where the values between the null cells are the same, so the table should look like this:

Date	SKU 1	SKU 2	SKU3	SKU 4
10/10/21	null	75	54	85
10/11/21	25	null	54	null
10/12/21	25	null	54	87
10/13/21	25	71	54	87
10/14/21	25	71	54	87
10/15/21	null	71	null	87
10/16/21	21	71	null	85
10/17/21	null	71	null	null

Is there any way to do it?

I would really appreciate the help 🙂

Need Help

lbendlin

4 years ago

This is a bit of a frankenscript but it does what you ask for.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQByIjQyUdJSAyNwUSpiZAwsJUKVYHIm0IkTYyhagBS8MljeB6IcjCHC5ljDAWpgQmZYLPSFPcRppB9SHkkdxpjsW+WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, SKU1 = _t, SKU2 = _t, SKU3 = _t, SKU4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "SKU", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"SKU", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", 
        each Table.Combine({ Table.FromRecords({Table.Last(Table.SelectRows(#"Sorted Rows",(k)=> k[SKU]=[SKU] and k[Value]>"" and k[Date]<[Date] ))}) , 
        Table.FromRecords({ Table.First(Table.SelectRows(#"Sorted Rows",(k)=> k[SKU]=[SKU] and k[Value]>"" and k[Date]>[Date] ))})})),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "NewValue", each if [Value]>"" then [Value] else try
 if [Custom]{0}[Value]=[Custom]{1}[Value] then [Custom]{0}[Value] else ""
 otherwise ""),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Date", "SKU", "NewValue"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[SKU]), "SKU", "NewValue")
in
    #"Pivoted Column"

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".

3 Replies

lbendlin

Super User

4 years ago

This is a bit of a frankenscript but it does what you ask for.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQByIjQyUdJSAyNwUSpiZAwsJUKVYHIm0IkTYyhagBS8MljeB6IcjCHC5ljDAWpgQmZYLPSFPcRppB9SHkkdxpjsW+WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, SKU1 = _t, SKU2 = _t, SKU3 = _t, SKU4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "SKU", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"SKU", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", 
        each Table.Combine({ Table.FromRecords({Table.Last(Table.SelectRows(#"Sorted Rows",(k)=> k[SKU]=[SKU] and k[Value]>"" and k[Date]<[Date] ))}) , 
        Table.FromRecords({ Table.First(Table.SelectRows(#"Sorted Rows",(k)=> k[SKU]=[SKU] and k[Value]>"" and k[Date]>[Date] ))})})),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "NewValue", each if [Value]>"" then [Value] else try
 if [Custom]{0}[Value]=[Custom]{1}[Value] then [Custom]{0}[Value] else ""
 otherwise ""),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Date", "SKU", "NewValue"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[SKU]), "SKU", "NewValue")
in
    #"Pivoted Column"

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".

Syndicate_Admin

Administrator

4 years ago

This is a bit of a frankenscript but it does what you ask for.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQByIjQyUdJSAyNwUSpiZAwsJUKVYHIm0IkTYyhagBS8MljeB6IcjCHC5ljDAWpgQmZYLPSFPcRppB9SHkkdxpjsW+WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, SKU1 = _t, SKU2 = _t, SKU3 = _t, SKU4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "SKU", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"SKU", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", 
        each Table.Combine({ Table.FromRecords({Table.Last(Table.SelectRows(#"Sorted Rows",(k)=> k[SKU]=[SKU] and k[Value]>"" and k[Date]<[Date] ))}) , 
        Table.FromRecords({ Table.First(Table.SelectRows(#"Sorted Rows",(k)=> k[SKU]=[SKU] and k[Value]>"" and k[Date]>[Date] ))})})),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "NewValue", each if [Value]>"" then [Value] else try
 if [Custom]{0}[Value]=[Custom]{1}[Value] then [Custom]{0}[Value] else ""
 otherwise ""),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Date", "SKU", "NewValue"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[SKU]), "SKU", "NewValue")
in
    #"Pivoted Column"

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".

wdx223_Daniel

Community Champion

4 years ago

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom1 = Table.UnpivotOtherColumns(Source,{"Date"},"SKU","Value"),
    Custom2 = Table.Combine(Table.Group(Custom1,"SKU",{"n",each Table.Combine(Table.Group(Table.Sort(_,"Date"),"Value",{"n",each #table(Table.ColumnNames(_),List.Transform({Number.From(List.Min([Date]))..Number.From(List.Max([Date]))},(x)=>{Date.From(x),[SKU]{0},[Value]{0}}))},0,(x,y)=>Byte.From(x<>y))[n])})[n]),
    Custom3 = Table.Pivot(Custom2,List.Distinct(Custom2[SKU]),"SKU","Value")
in
    Custom3

Forum Discussion

Fill Down only when the limit values are the same

3 Replies

Related content

Copilot, limitations?

Filled map

Data limit Issues

Limit 1000 values

DAX for filling down a measure's blank values