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

Question

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

chillin chillin · Accepted Answer · 2019-06-08T20:13:19

Tested this in Power Query for Excel, but hopefully should work for you in Power BI too. If you create a function like:

divisionToList = (numberToDivide as number, numberToDivideBy as number) as list =>
    let
        divisionResult = numberToDivide / numberToDivideBy,
        isResultValid = (divisionResult >= 0) and (Number.Mod(divisionResult, 1) = 0),
        errorIfInvalid = Error.Record("Cannot create a list with " & Text.From(divisionResult) & " items", Number.ToText(numberToDivide) & " / " & Number.ToText(numberToDivideBy) & " = " & Text.From(divisionResult), null),
        listOrError = if isResultValid then List.Repeat({divisionResult}, divisionResult) else error errorIfInvalid
    in listOrError,

It should divide two numbers and return a list of length d in which each element is d (d is the result of the division). This list can then, in the context of a table, be expanded into new rows.

There is some basic error handling in the function for cases where the division yields a problematic number (since you can't have a list with, for example, 5.1 elements or -1 elements). You can change/remove this handling if necessary.

I think this code below takes me from your first image to your second image -- and hopefully will give you some idea on how to go about achieving this.

let
    mockData = Table.FromColumns({{200, 400}, {"A", "B"}}, type table [Amount = number, Description = text]),
    defaultValue = 50, // Not sure what logic is required for arriving at this figure, so have simply assigned it.
    divisionToList = (numberToDivide as number, numberToDivideBy as number) as list =>
        let
            divisionResult = numberToDivide / numberToDivideBy,
            isResultValid = (divisionResult >= 0) and (Number.Mod(divisionResult, 1) = 0),
            errorIfInvalid = Error.Record("Cannot create a list with " & Text.From(divisionResult) & " items", Number.ToText(numberToDivide) & " / " & Number.ToText(numberToDivideBy) & " = " & Text.From(divisionResult), null),
            listOrError = if isResultValid then List.Repeat({divisionResult}, divisionResult) else error errorIfInvalid
        in listOrError,
    invokeFunction = Table.TransformColumns(mockData, {{"Amount", each divisionToList(_, defaultValue), type list}}),
    expanded = Table.ExpandListColumn(invokeFunction, "Amount")
in
    expanded

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

1 Answers