Forum Discussion
Solved
Add Dynamic Rows in Power Query
Thanks Advanced !!! I have below datset with dens value, in this dataset dens value with missing contunious number. MattAllington Greg_Deckler Zubair_Muhammad
Sorry Baskar,
my fault. Here it comes:
let CreateRowsFunction = (Partition) => let Source = Partition, AddedIndex = Table.AddIndexColumn(Source, "Index", 0, 1), #"Added Custom" = Table.AddColumn(AddedIndex, "Custom", each try { [Dense Value] .. AddedIndex[Dense Value]{[Index]+1}-1 } otherwise {[Dense Value]}), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each if [Custom]=[Dense Value] or [Custom] = null then [Count] else 0) in #"Added Custom1", Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc+xEQAhCATAXogNHk5RanHsv40HP/EluWBn4GBOsgcM61TIRDxZaJUf4/FEZvbMGjvGOHgECDwrEmtwu3k3StIoRD3YHLpGY7u1xwrVxLxHEn+fI/k+2y9ZLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Ticket = _t, #"Dense Value" = _t, Count = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dense Value", Int64.Type}, {"Count", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Ticket"}, {{"Partition", each _, type table}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each CreateRowsFunction([Partition])), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Custom", "Custom.1"}, {"Dense Value", "Count"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Partition"}) in #"Removed Columns"Please check out this new code:
let CreateRowsFunction = (Partition) => let Source = Table1, #"Sorted Rows" = Table.Sort(Source,{{"Dense Value", Order.Ascending}}), AddedIndex = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1), Custom1 = Table.AddColumn(AddedIndex, "Intervals", each try (AddedIndex[Dense Value]{[Index]+1}*10 - [Dense Value]*10) / 2 otherwise 0), #"Added Custom1" = Table.AddColumn(Custom1, "Custom", each {0..[Intervals] - 1}), #"Added Custom" = Table.AddColumn(#"Added Custom1", "Add", each List.Transform([Custom], (x) => x * 0.2)), #"Expanded Add" = Table.ExpandListColumn(#"Added Custom", "Add"), #"Replaced Value" = Table.ReplaceValue(#"Expanded Add",null,0,Replacer.ReplaceValue,{"Add"}), #"Inserted Addition" = Table.AddColumn(#"Replaced Value", "NewDense", each [Dense Value] + [Add], type number), #"Removed Other Columns" = Table.SelectColumns(#"Inserted Addition",{"Ticket", "Dense Value", "Count", "NewDense"}) in #"Removed Other Columns" , Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI20jMCUkZKsTpwATMQhRAw1rMAUiYIARMgYYrEBWswQxYAaTBHCJiiypuCrTRFFkCz0hRsAtBRsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Ticket = _t, #"Dense Value" = _t, Count = _t]), #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Dense Value", type number}, {"Count", type number}}), #"Grouped Rows" = Table.Group(#"Changed Type1", {"Ticket"}, {{"Partition", each _, type table}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each CreateRowsFunction([Partition])), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Dense Value", "Count", "NewDense"}, {"Dense Value", "Count", "NewDense"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Partition"}) in #"Removed Columns"
1) Add an index column to "fake" a row index, starting from 0
2) Add a column where you create a list of values that cover the range up until the next row like this:
{ [Dense Value] .. <YourPreviousStepName>[Dense Value]{[Index]+1}-1 }3) Expand this list-column. It will return the missing values. You can then add a column for a new value where you compare if the value of the new column equals column "Dense Value". If so, take the original amount and if not, take 0.
Thanks for your valuable response ImkeF.
Am begginer on power query, I will explain in little bit brief on my problem.
Current vs Expected
Can you please help me to here. stucked with last 3 days with this scenario.
Thanks a lot.
You need a function for it. Please post link to sample data and I will mockup a solution for you.
Hi Baskar,
please paste this code into the advanced editor:
let CreateRowsFunction = (Partition) => let Source = Partition, AddedIndex = Table.AddIndexColumn(Source, "Index", 0, 1), #"Added Custom" = Table.AddColumn(AddedIndex, "Custom", each try { [Dense Value] .. AddedIndex[Dense Value]{[Index]+1}-1 } otherwise {}), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each if [Custom]=[Dense Value] or [Custom] = null then [Count] else 0) in #"Added Custom1", Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc+xEQAhCATAXogNHk5RanHsv40HP/EluWBn4GBOsgcM61TIRDxZaJUf4/FEZvbMGjvGOHgECDwrEmtwu3k3StIoRD3YHLpGY7u1xwrVxLxHEn+fI/k+2y9ZLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Ticket = _t, #"Dense Value" = _t, Count = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dense Value", Int64.Type}, {"Count", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Ticket"}, {{"Partition", each _, type table}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each CreateRowsFunction([Partition])), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Dense Value", "Custom.1"}, {"Dense Value", "Count"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Partition"}) in #"Removed Columns"