Consolidating data (data modeling issue)

[Appe]

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

[Phil_Seamark · Accepted 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"

[Phil_Seamark · Accepted solution]

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

[Phil_Seamark · Accepted 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"

[Appe]

Thank you very much! Seems like I was trying a bit too hard with Dax.. Will give this a go this afternoon!

Cheers,

Anders

[v-ljerr-msft]

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

[Phil_Seamark]

Hi @Appe

This is what it should hopefully look like