Forum Discussion
Solved
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 :
Thank you very much for your help !
Best regards
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"
4 Replies
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)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"
