Hello everyone!
I am working on a report in Power BI and need help modifying an M code. I have a data table that includes insurance information, and in some specific cases, I need to split the values of a policy into several new rows based on different insurers and percentages.
Here is the scenario:
I have a row in my table where the client is "SUPER STORES LTD" and the policy is "12345678".
For this row, I want to remove the original row and add new rows with values divided according to the following percentage distribution:
- SEGURO PLUS: 20%
- PROTECTOR INSURANCE: 15%
- FORTUNE INSURANCE: 10%
- GUARANTOR INSURANCE: 5%
- RELIANCE INSURANCE: 25%
- TRUSTY INSURANCE: 10%
- SAFEWAY INSURANCE: 15%
The values of "Premium" and "Commission" should be divided according to the provided percentages.
Here is the M code I am currently using:
let
// Load the Excel file from a URL
Source = Excel.Workbook(Web.Contents("URL_OF_THE_FILE"), null, true),
// Select the "RptAnaliseProducao" sheet from the Excel file
RptAnaliseProducao_Sheet = Source{[Item="RptAnaliseProducao",Kind="Sheet"]}[Data],
// Promote the first row to headers
#"Promoted Headers" = Table.PromoteHeaders(RptAnaliseProducao_Sheet, [PromoteAllScalars=true]),
// Set the data types of date columns
#"Set Column Type to DATE" = Table.TransformColumnTypes(#"Promoted Headers",{{"Data Proposta", type date}, {"Data Conferência", type date}, {"Início de Vigência", type date}}),
// Set the data types of value columns
#"Set Column Type VALUE" = Table.TransformColumnTypes(#"Set Column Type to DATE",{{"Premium", Currency.Type}, {"Percentage", Percentage.Type}, {"Commission", Currency.Type}}),
// Filter out the specific row with policy 12345678 and client SUPER STORES LTD
#"Filtered Specific Row" = Table.SelectRows(#"Set Column Type VALUE", each not ([Policy] = "12345678" and [Client] = "SUPER STORES LTD")),
// Add new rows with divided values
#"Add Divided Rows" = Table.Combine({
#"Filtered Specific Row",
let
OriginalRow = Table.SelectRows(#"Set Column Type VALUE", each [Policy] = "12345678" and [Client] = "SUPER STORES LTD"),
OriginalPremium = OriginalRow{0}[Premium],
OriginalCommission = OriginalRow{0}[Commission],
Percentages = [
{"SEGURO PLUS", 0.20},
{"PROTECTOR INSURANCE", 0.15},
{"FORTUNE INSURANCE", 0.10},
{"GUARANTOR INSURANCE", 0.05},
{"RELIANCE INSURANCE", 0.25},
{"TRUSTY INSURANCE", 0.10},
{"SAFEWAY INSURANCE", 0.15}
],
AddRow = List.Transform(Percentages, each [
Client = OriginalRow{0}[Client],
Business Type = OriginalRow{0}[Business Type],
Branch = OriginalRow{0}[Branch],
Product = OriginalRow{0}[Product],
Cell = OriginalRow{0}[Cell],
"Start of Validity" = OriginalRow{0}[Start of Validity],
"Proposal Date" = OriginalRow{0}[Proposal Date],
"Conference Date" = OriginalRow{0}[Conference Date],
Endorsement = OriginalRow{0}[Endorsement],
Insurer = _{0},
Premium = OriginalPremium * _{1},
Percentage = OriginalRow{0}[Percentage],
Commission = OriginalCommission * _{1},
Proposal = OriginalRow{0}[Proposal],
Policy = OriginalRow{0}[Policy],
Status = OriginalRow{0}[Status],
PRODUCER = OriginalRow{0}[PRODUCER],
"THIRD-PARTY PRODUCER" = OriginalRow{0}[THIRD-PARTY PRODUCER],
UNIT = OriginalRow{0}[UNIT],
"MODIFIED UNIT" = OriginalRow{0}[MODIFIED UNIT],
SUBPRODUCER = OriginalRow{0}[SUBPRODUCER],
OPERATION = OriginalRow{0}[OPERATION],
PRODUCTION = OriginalRow{0}[PRODUCTION],
BROKERAGE = OriginalRow{0}[BROKERAGE],
Campaign = OriginalRow{0}[Campaign],
DOCUMENT = OriginalRow{0}[DOCUMENT]
])
in
Table.FromRecords(AddRow)
}),
// Adjust the data types of new rows
#"Adjusted Data Types" = Table.TransformColumnTypes(#"Add Divided Rows",{{"Premium", Currency.Type}, {"Commission", Currency.Type}})
in
#"Adjusted Data Types"
Issue:
- The original row with policy "12345678" and client "SUPER STORES LTD" was not removed.
- I need to ensure that the division of values (Premium and Commission) is done correctly based on the provided percentages.
Question:
- How can I adjust this code to ensure that the row with policy "12345678" and client "SUPER STORES LTD" is removed and replaced with new rows with divided values according to the percentages?
Thank you for any help or suggestions!
