Consolidating data (data modeling issue)

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