Is Power Query (M) able to achieve this requirement (or DAX for that matter)?

[Anonymous]

Hi,

I have received a requirement to do the following.

The original dataset is structured as this:
Code       Date                 Cat1       Cat2       Cat3       Cat4       Team
9331        01/06/2016      .1.1a        null         .1.1b       null         Operations

8366        01/06/2016      null         null         .3.7f        .1.1a        Operations

9331        01/07/2016      .2.1a        null         null         null         Marketing

The requirement is two-fold.

The first part is to take the above data and produce the below dataset (notice, below, how there are no rows for any null values from the above).

Code       Date                 Cat        Team

9331        01/06/2016      .1.1a       Operations

9331        01/06/2016      .1.1b       Operations

8366        01/06/2016      .3.7f        Operations

8366        01/06/2016      .1.1a       Operations

9331        01/07/2016      .2.1a       Marketing

The second part of the requirement is create a new table per Code and Date.

So the tables will be created as follows:

Table 1

Code       Date                 Cat        Team

9331        01/06/2016      .1.1a       Operations

9331        01/06/2016      .1.1b       Operations

Table 2

Code       Date                 Cat        Team

8366        01/06/2016      .3.7f        Operations

8366        01/06/2016      .1.1a       Operations

Table 3
Code       Date                 Cat        Team

9331        01/07/2016      .2.1a       Marketing

I hope what I've explain makes sense and you can see how the data needs to be transformed.

Can both parts be acheived, or only the first part, or only the second part?

If so can it be done using Power Query (M), or DAX, or both?

Finally, how do you do it?

Thanks in advance.

[Greg_Deckler · Accepted solution]

For the first part:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQ2NlTSUTIw1Dcw0zcyMDQDcvQM9QwTgTSUmQRh+hekFiWWZObnFSvF6kQrWRibmaFrhOox1jNPQzIGTSOSjeZwG43gNkKQb2JRdmpJZl66UmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Code = _t, Datae = _t, Cat1 = _t, Cat2 = _t, Cat3 = _t, Cat4 = _t, Team = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}, {"Datae", type date}, {"Cat1", type text}, {"Cat2", type text}, {"Cat3", type text}, {"Cat4", type text}, {"Team", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Code", "Datae", "Team"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> "")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Value", "Cat"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute"})
in
    #"Removed Columns"

For the second part, perhaps with a custom M function? I guess I would question why you would need separate tables, that's generally an anti-pattern with Power BI.

[Greg_Deckler · Accepted solution]

For the first part:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQ2NlTSUTIw1Dcw0zcyMDQDcvQM9QwTgTSUmQRh+hekFiWWZObnFSvF6kQrWRibmaFrhOox1jNPQzIGTSOSjeZwG43gNkKQb2JRdmpJZl66UmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Code = _t, Datae = _t, Cat1 = _t, Cat2 = _t, Cat3 = _t, Cat4 = _t, Team = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}, {"Datae", type date}, {"Cat1", type text}, {"Cat2", type text}, {"Cat3", type text}, {"Cat4", type text}, {"Team", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Code", "Datae", "Team"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> "")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Value", "Cat"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute"})
in
    #"Removed Columns"

For the second part, perhaps with a custom M function? I guess I would question why you would need separate tables, that's generally an anti-pattern with Power BI.

[Anonymous]

smoupre - thanks for your response.

When doing as you've scripted the null values are still present. I thought there would be a way to delete them, but it seems all I can do is filter out the null values and click 'Close and Apply'. This way the null values are not present in the Desktop/DAX engine - so all is fine. Just wanted to check if I'm not missing something that I should be able to delete them.

[MarcelBeug]

In the example of @Greg_Deckler the table has no null values, but empty values.

If you have null values, then these disappear while unpivoting.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQ2NlTSUTIw1Dcw0zcyMDQDcvQM9QwTgTSUmQRh+hekFiWWZObnFSvF6kQrWRibmaFrhOox1jNPQzIGTSOSjeZwG43gNkKQb2JRdmpJZl66UmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Code = _t, Datae = _t, Cat1 = _t, Cat2 = _t, Cat3 = _t, Cat4 = _t, Team = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}, {"Datae", type date}, {"Cat1", type text}, {"Cat2", type text}, {"Cat3", type text}, {"Cat4", type text}, {"Team", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,Table.ColumnNames(#"Changed Type")),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Code", "Datae", "Team"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Cat"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute"})
in
    #"Removed Columns"

How about my answer to your second question?

[Anonymous]

MarcelBeug - I'm currently looking at your response. Trying to figure out a few things out before I respond, in particular, creating a record. I will have to play around a bit more. Thanks for your response.

As for the nulls disppearnig when I unpivot - they didn't. I have to filter out the nulls. However, one thing I must check is what was Power BI seeing the nulls as. Maybe the nulls had been converted to text and therefore Power BI didn't see fit to remove them when unpivoting. I will have to double check.

[MarcelBeug · Accepted solution]

For the second part: a query can only return 1 value (which can be a single value, a list, a table, a record or a function, each with all kinds of nesting options).

The closest you can get at this moment is to create a record with the separate tables, but this is only usable within Power Query and won't help you with any follow up in DAX/reporting.

You may want to vote for this idea.