cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
antheoh Visitor
Visitor

Split row in multiple other rows in Power Bi based on a division of a number

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 :

enter image description here

if the default value we want to devide column Amount is 50,then the desirable result would be something like that :

enter image description here

Do you have any idea how can i implement that in Power query editor or with dax?

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Nolock Established Member
Established Member

Re: Split row in multiple other rows in Power Bi based on a division of a number

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
2 REPLIES 2
Nolock Established Member
Established Member

Re: Split row in multiple other rows in Power Bi based on a division of a number

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
Super User
Super User

Re: Split row in multiple other rows in Power Bi based on a division of a number

@antheoh 

 

Another way is to add this custom column and expand it to new rows

 

=List.Numbers([Amount]/50,[Amount]/50,0)