Help Insert rows based on condition + calculation

[Raph]

Hello ! 

Can someone please help me?

I would like to split rows where REF value = MPEMMA or REF/VALUE = MPSTD in new rows where : 

ANALY and AMOUTEUR will be modified in

DISTRIB with 25% of AMOUNTEUR

DISTRIM with 75% of AMOUTEUR

Like this : 

Thank you very much for your help !

Best regards

Raphaël

[PattemManohar]

@Raph  Please try this as a New Table

Test292Out = 
VAR _TableDISTRIB = SELECTCOLUMNS(FILTER(Test292SplitRows,Test292SplitRows[ANALY] IN {"MPEMMA","MPSTD"}), "ACCOUNTGL",Test292SplitRows[ACCOUNTGL],"REF",Test292SplitRows[REF],"DATE",[DATE],"AMOUNTEUR",Test292SplitRows[AMOUNTEUR]*0.25,"ANALY","DISTRIB")
VAR _TableDISTRIM = SELECTCOLUMNS(FILTER(Test292SplitRows,Test292SplitRows[ANALY] IN {"MPEMMA","MPSTD"}), "ACCOUNTGL",Test292SplitRows[ACCOUNTGL],"REF",Test292SplitRows[REF],"DATE",[DATE],"AMOUNTEUR",Test292SplitRows[AMOUNTEUR]*0.75,"ANALY","DISTRIM")
VAR _TableOther = FILTER(Test292SplitRows,NOT Test292SplitRows[ANALY] IN {"MPEMMA","MPSTD"})
RETURN UNION(_TableDISTRIB,_TableDISTRIM,_TableOther)

[Raph]

Thank you for your answer, @PattemManohar

I should have specified I was looking for a solution in Power Query. Would it be possible to do the same in M Language?  

Thank again

Raphaël

[PattemManohar · Accepted solution]

@Raph  That's fine !! Here is the M-code for the steps that was followed to achieve the same result in Power Query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjMyMDUwUNJR8g1w9fV1BDKMDfUNLPSNDAwtgBwUuVgdkHpDQ6hYhK8nSLmBvoElTDlQCiEHVm5obAEVCg5xgZpuDlNugiQFUW0CszDIxxNdtQWSVGwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ACCOUNTGL = _t, REF = _t, DATE = _t, AMOUNTEUR = _t, ANALY = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ACCOUNTGL", Int64.Type}, {"REF", type text}, {"DATE", type date}, {"AMOUNTEUR", Int64.Type}, {"ANALY", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "DISTRIB", each if [ANALY] = "MPEMMA" then "DISTRIB" else if [ANALY] = "MPSTD" then "DISTRIB" else [ANALY]),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "DISTRIM", each if [ANALY] = "MPEMMA" then "DISTRIM" else if [ANALY] = "MPSTD" then "DISTRIM" else [ANALY]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Conditional Column1", {"ACCOUNTGL", "REF", "DATE", "AMOUNTEUR", "ANALY"}, "Attribute", "Value"),
    #"Removed Duplicates" = Table.Distinct(#"Unpivoted Columns"),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"ANALY", "Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "ANALY"}}),
    #"Removed Duplicates1" = Table.Distinct(#"Renamed Columns"),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates1", "AMOUNTEURNew", each if [ANALY] = "DISTRIB" then [AMOUNTEUR]*0.25 else if [ANALY]="DISTRIM" then [AMOUNTEUR]*0.75 else [AMOUNTEUR]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"AMOUNTEUR"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"AMOUNTEURNew", "AMOUNTEUR"}})
in
    #"Renamed Columns1"

[Raph]

@PattemManohar 

Thank you very much, it works perfectly.

I've added an index column at the beginning to be sure to not lose information when we remove duplicates.

Thanks again !

Raphaël