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.
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.
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 ) )
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))
Hi @unnipbi123 ,
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
Proud to be a Datanaut!
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.