Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Appe
Frequent Visitor

Consolidating data (data modeling issue)

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.

 

Problem.PNG

 

Lösning.PNG

 

Any help is appreciated.

 

Many thanks,

Anders

2 ACCEPTED SOLUTIONS
Phil_Seamark
Employee
Employee

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

 

Duplicate.png

 

Now filter the first table by removing the empty rows from the Margin Column

 

remove empty.png

 

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

 

merge2.png

 

Click Expand and choose the columns you want and finally remove all the columns you don't need

 

expand.png

 

this is the result

 

result2.png

 

 

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"

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Phil_Seamark
Employee
Employee

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
Phil_Seamark
Employee
Employee

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Phil_Seamark
Employee
Employee

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

 

Duplicate.png

 

Now filter the first table by removing the empty rows from the Margin Column

 

remove empty.png

 

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

 

merge2.png

 

Click Expand and choose the columns you want and finally remove all the columns you don't need

 

expand.png

 

this is the result

 

result2.png

 

 

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"

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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. Smiley Happy

 

Regards

Hi @Appe

 

This is what it should hopefully look like

 

DAX join.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.