Create a dynamic row for the table in Power query based on the column value

[Anonymous]

Hello all,

I have an invoice report which has the data in .csv file. I am importing this csv file into power bi to create reports. The table structure of the file is as below.

Total amount is the addition of Email cost + Accounting cost

Now the challenge that I have is, I need to separate the Accounting cost from this and so it as a separate product under product name.

So the row needs to be created for the same organization with the product name as “Accounting” and show the accounting cost of $200 under that. also from the total amount for product name “Email”, $200 of accounting cost needs to be reduced.

After I make changes, my table should look like below.

Please let me know if there is any idea to achieve this in Power query editor.

[kirete17]

Custom1 = 
        List.TransformMany(
            Table.ToRows( PreStep ),
            each
                let
                    l = List.Range( _, 4, List.Count( _ ) - 5 ),
                    c = List.Skip( Table.ColumnNames( PreStep ), 4 )
                in
                    List.Transform(
                        List.Positions( l ),
                        (x)=> { Text.BeforeDelimiter( c{x}, " " ) } & List.Repeat( {null}, x ) & { l{x} } & List.Repeat( {null}, List.Count( l ) -x -1 ) & { l{x} }
                    ),
            (x,y)=> List.FirstN( x, 2 ) & {y{0}, x{3} } & List.Skip( y )
        ),
    Custom2 = 
        Table.FromRows( Custom1, Table.ColumnNames( PreStep ) )

[wdx223_Daniel]

NewStep=Table.FromRecords(List.TransformMany(Table.ToRecords(PreviousStepName),each if [Accounting Cost]=0 or [Accounting Cost]= null then {[]} else {[#"Accounting Cost"=null,#"Total Amount"=[Email Cost]],[#"Product Name"="Accounting",#"Email Cost"=null, #"Total Amount"=[Accounting Cost]]},(x,y)=>x&y))

[BA_Pete]

Hi @Anonymous ,

As this data is to be used in Power BI, then the 'correct' way to handle this is as follows:

-1- Remove your [Total Amount] and [Product Name] columns. In the example query below I've used 'Remove Other Columns' (remOthCols step).

-2- Rename your [Accounting Cost] and [Email Cost] columns to just 'Accounting' and 'Email', as these will become your new [Product Name] column.

-3- Multi-select (Ctrl+click) your [Ord ID], [Org Name], and [Invoice ID] columns (and any others you have that aren't [Email] or [Accounting]).

-4- Go to the Transform tab > Unpivot Columns (dropdown) > Unpivot Other Columns

-5- Amend the generated unpivot code in the formula bar to name the new [Attribute] and [Value] columns with friendlier names, like this:

...goes to:

This now gives the optimal data structure for reporting in Power BI.

Once you've sent it to the data model, you would create measures like this for visualisation:

_productCost_email =        // For use in cards etc.
CALCULATE(
    SUM(yourTable[Amount]),
    yourTable[Product Name] = "Email"
)

_productCost_accounting =   // For use in cards etc.
CALCULATE(
    SUM(yourTable[Amount]),
    yourTable[Product Name] = "Accounting"
)

_productCost_total =       // For use in any visual where other dimensions are used for context
SUM(yourTable[Amount])

Example query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwNDZT0lEKTk1JylTIzEvWA3JccxMzc4C0oaWloSmQNjKxNDMwBDEMDCBcCyA3NhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Org ID" = _t, #"Org Name" = _t, #"Poduct Name" = _t, #"Invoice Id" = _t, #"Email Cost" = _t, #"Accounting Cost" = _t, #"Total Amount" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Org ID", Int64.Type}, {"Org Name", type text}, {"Poduct Name", type text}, {"Invoice Id", Int64.Type}, {"Email Cost", Int64.Type}, {"Accounting Cost", Int64.Type}, {"Total Amount", Int64.Type}}),

    remOthCols = Table.SelectColumns(chgTypes,{"Org ID", "Org Name", "Invoice Id", "Email Cost", "Accounting Cost"}),
    renameCols = Table.RenameColumns(remOthCols,{{"Email Cost", "Email"}, {"Accounting Cost", "Accounting"}}),
    unpivOthCols = Table.UnpivotOtherColumns(renameCols, {"Org ID", "Org Name", "Invoice Id"}, "Product Name", "Amount")

in
    unpivOthCols

Pete