Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Raph
Helper III
Helper III

Help Insert rows based on condition + calculation

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 : 

 

ExPQ.JPG

 

Thank you very much for your help !

 

Best regards

 

Raphaël

1 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"

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




View solution in original post

4 REPLIES 4
PattemManohar
Community Champion
Community Champion

@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)

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




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

@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"

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.