Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Thanks Advanced !!!
I have below datset with dens value, in this dataset dens value with missing contunious number.
@MattAllington @Greg_Deckler @Zubair_Muhammad
Solved! Go to Solution.
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @Baskar
Sorry I am not very good in Power Query
But this DAX calculated table should work
Calculated Table = VAR temp = GENERATE ( VALUES ( Table1[Ticket_INTL_ID] ), VAR maxvalue = MAXX ( RELATEDTABLE ( Table1 ), [Dens_Value] ) VAR minvalue = MINX ( RELATEDTABLE ( Table1 ), [Dens_Value] ) RETURN SELECTCOLUMNS ( EXCEPT ( GENERATESERIES ( minvalue, maxvalue ), CALCULATETABLE ( VALUES ( Table1[Dens_Value] ) ) ), "Dense_Value", [Value] ) ) VAR temp1 = ADDCOLUMNS ( temp, "Dense Count", 0 ) RETURN UNION ( Table1, temp1 )
I am attaching the sample file as well
Tagging the Power Query Champs
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thanks for your valuable response @ImkeF.
Am begginer on power query, I will explain in little bit brief on my problem.
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @ImkeF ,
You are the real champion of Power Query. Tons of Thanks from me.
Need one clarification here. Is there any way to get continuous number in Dense instead of same number.
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
No words to express my happiness. @ImkeF
Your the real guru in Power Query.
You are Champions of Champion.
Thank you Baskar!
Such a feedback keeps me going 😉
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @ImkeF
Sorry for asking question on this same issue. Now have to increase the value .2 in dense value.
I tried with your existing code but no luck for me. Can you please help me on this case.
Expected result attached in image.
Hi @Baskar,
hope this works, otherwise please post link to sample data:
let CreateRowsFunction = (Partition) => let Source = Partition, #"Sorted Rows" = Table.Sort(Source,{{"Dense Value", Order.Ascending}}), AddedIndex = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1), #"Added Custom" = Table.AddColumn(AddedIndex, "Add", each try List.Transform( {1..AddedIndex[Dense Value]{[Index]+1} - [Dense Value] }, (x) => (x-1) * 0.2) otherwise {0}), #"Expanded Add" = Table.ExpandListColumn(#"Added Custom", "Add") in #"Expanded Add", 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 Custom1" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Dense Value", "Count", "Add"}, {"Dense Value", "Count", "Add"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom1",{"Partition"}), #"Inserted Addition" = Table.AddColumn(#"Removed Columns", "Result", each [Dense Value] + [Add], type number) in #"Inserted Addition"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thanks for very quick response @Zubair_Muhammad.
Here am looking for power query operation. after that i have few ETL operations.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |