Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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"
Proud to be a PBI 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)
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"
Proud to be a PBI Community Champion
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |