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.
In Power BI Desktop i have a table from an excel file and i want to split a row based on a division between the value of a specific column and a default number.
In more details lets assume tha we have a table like this :
if the default value we want to devide column Amount is 50,then the desirable result would be something like that :
Do you have any idea how can i implement that in Power query editor or with dax?
Thanks
Solved! Go to Solution.
Hi @antheoh,
you can create a list for every line and than expand this list into rows.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwUNJRclSK1YlWMgGznZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Amount = _t, Description = _t]), ChangeType = Table.TransformColumnTypes(Source,{{"Amount", Int64.Type}, {"Description", type text}}), // your denominator Denominator = 50, // generates a list of repeated values fnGenerateList = (value as number, denominator as number) as list => let Count = value / denominator, List = List.Numbers(Count, Count, 0) in List, NewAmountColumn = Table.AddColumn(ChangeType, "NewAmountColumn", each fnGenerateList([Amount], Denominator)), ExpandedNewAmountColumn = Table.ExpandListColumn(NewAmountColumn, "NewAmountColumn"), RemoveOldAmountColumn = Table.RemoveColumns(ExpandedNewAmountColumn,{"Amount"}), RenameNewAmountColumn = Table.RenameColumns(RemoveOldAmountColumn,{{"NewAmountColumn", "Amount"}}) in RenameNewAmountColumn
Another way is to add this custom column and expand it to new rows
=List.Numbers([Amount]/50,[Amount]/50,0)
Hi @antheoh,
you can create a list for every line and than expand this list into rows.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwUNJRclSK1YlWMgGznZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Amount = _t, Description = _t]), ChangeType = Table.TransformColumnTypes(Source,{{"Amount", Int64.Type}, {"Description", type text}}), // your denominator Denominator = 50, // generates a list of repeated values fnGenerateList = (value as number, denominator as number) as list => let Count = value / denominator, List = List.Numbers(Count, Count, 0) in List, NewAmountColumn = Table.AddColumn(ChangeType, "NewAmountColumn", each fnGenerateList([Amount], Denominator)), ExpandedNewAmountColumn = Table.ExpandListColumn(NewAmountColumn, "NewAmountColumn"), RemoveOldAmountColumn = Table.RemoveColumns(ExpandedNewAmountColumn,{"Amount"}), RenameNewAmountColumn = Table.RenameColumns(RemoveOldAmountColumn,{{"NewAmountColumn", "Amount"}}) in RenameNewAmountColumn
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.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |