Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Solved! Go to Solution.
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".
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
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".
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".
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.