Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a table with goals throughout the year start with the first every month. The problem is that I have two separate goals for each month and user and currently they are written on two separate rows. What I want to do is to consolidate the two goals so that they are written on the same row.
Please see below for current and desired outcome.
Any help is appreciated.
Many thanks,
Anders
Solved! Go to Solution.
Hi @Appe
You can do this in the Query Editor easily enough.
First, duplicate your table by right clicking it in the left menu and clicking duplicate. This creates 2 versions of your table
Now filter the first table by removing the empty rows from the Margin Column
Repeat on the 2nd table but remove empty from the Sales column
Now Merge the two tables using Merge Query, pick the two tables and click the first two columns for each table
Click Expand and choose the columns you want and finally remove all the columns you don't need
this is the result
here is the M for reference
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNNc1MAQiJR0lr/yMPAUQUZyfB+TqGRsBSaVYHZgyI4gy58SiHAUQAVVmaIyqzBhqWmauAhBDFRmZoSrCbiUQGRoZELQSpMzYlICVIEWGQLNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Name = _t, Margin = _t, Sales = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Name", type text}, {"Margin", type number}, {"Sales", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each true), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Margin] <> null and [Margin] <> ""), #"Merged Queries" = Table.NestedJoin(#"Filtered Rows1",{"Date", "Name"},#"Table2 (2)",{"Date", "Name"},"NewColumn",JoinKind.LeftOuter), #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Sales"}, {"NewColumn.Sales"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumn",{"Sales"}) in #"Removed Columns"
And a DAX solution might look like this. Create a new table from the Modeling tab and paste in the following (assuming your base table is called Current
New Table = NATURALLEFTOUTERJOIN( SELECTCOLUMNS(FILTER('Current',[Sales]<> Blank()),"Date",[Date],"Name",[Name],"Sales",[Sales]), SELECTCOLUMNS(FILTER('Current',[Margin]<> Blank()),"Date",[Date],"Name",[Name],"Margin",[Margin]) )
And a DAX solution might look like this. Create a new table from the Modeling tab and paste in the following (assuming your base table is called Current
New Table = NATURALLEFTOUTERJOIN( SELECTCOLUMNS(FILTER('Current',[Sales]<> Blank()),"Date",[Date],"Name",[Name],"Sales",[Sales]), SELECTCOLUMNS(FILTER('Current',[Margin]<> Blank()),"Date",[Date],"Name",[Name],"Margin",[Margin]) )
Hi @Appe
You can do this in the Query Editor easily enough.
First, duplicate your table by right clicking it in the left menu and clicking duplicate. This creates 2 versions of your table
Now filter the first table by removing the empty rows from the Margin Column
Repeat on the 2nd table but remove empty from the Sales column
Now Merge the two tables using Merge Query, pick the two tables and click the first two columns for each table
Click Expand and choose the columns you want and finally remove all the columns you don't need
this is the result
here is the M for reference
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNNc1MAQiJR0lr/yMPAUQUZyfB+TqGRsBSaVYHZgyI4gy58SiHAUQAVVmaIyqzBhqWmauAhBDFRmZoSrCbiUQGRoZELQSpMzYlICVIEWGQLNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Name = _t, Margin = _t, Sales = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Name", type text}, {"Margin", type number}, {"Sales", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each true), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Margin] <> null and [Margin] <> ""), #"Merged Queries" = Table.NestedJoin(#"Filtered Rows1",{"Date", "Name"},#"Table2 (2)",{"Date", "Name"},"NewColumn",JoinKind.LeftOuter), #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Sales"}, {"NewColumn.Sales"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumn",{"Sales"}) in #"Removed Columns"
Thank you very much! Seems like I was trying a bit too hard with Dax.. Will give this a go this afternoon!
Cheers,
Anders
Hi @Appe,
Have you tried the solution provided by Phil_Seamark? Have you tried the solution provided above? Does it work in your scenario? If you still have any question, feel free to post it here.
Regards
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |