Unpivot.... getting out of hands

[dc7669]

I have a data table that looks something like this: 

Name	Attribute1 (with 13 options, i.e. 13 columns)	Attribute2 (with 7 options, i.e. 7 columns)	Atttribue3 (with 10 options, i.e. 10 columns)	Attributes 4 (with 11 options, i.e. 11 columns)
Person1
Person2
....
Person100

On a PowerBI Report, I would like to have 4 slicers, each shows all the options for each attribute.  I learned from this Forum that I can use Unpivot to get to the all options of each attribute.  I can use this strategy 4 times over. Yet, with this data set, 100 rows of data becomes 1 million rows because 100 records x13 x 7 x 10 x 11 = 1 million 1 thousand. 

I wonder if there is any other more efficient/quicker way to attach this problem? 

Thanks! 

[v-gizhi-msft]

Hi,

@dc7669 

After copying and unpivoting, please try to create a slicer table to filter:

SlicerTable =
ADDCOLUMNS (
    UNION (
        DISTINCT ( 'Table (1)'[Attribute] ),
        DISTINCT ( 'Table (2)'[Attribute] ),
        DISTINCT ( 'Table (3)'[Attribute] ),
        DISTINCT ( 'Table (4)'[Attribute] )
    ),
    "Group", "Group-" & RIGHT ( LEFT ( [Attribute], 5 ), 1 )
)

The result shows:

See my attached pbix file.

Best Regards,

Giotto

[v-gizhi-msft]

Hi,

Please take following steps:

1)Copy the original table to four same tables in Query Editor first.

2)Reserve one attribute in one table and delete other attribute columns.

3)After apply, you can create four slicers to filter these four tables and each table will only have at most 130 rows.

Best Regards,

Giotto

[Greg_Deckler · Accepted solution]

I thought this too at first @v-gizhi-msft but the confusion stems from the post, it is not 4 columns as is indicated in the post. If you look a little closer, I believe the data is organized like the following:

Person,attribute1_value1,attribute1_value2,attribute1_value3,attribute2_value1,attribute2_value2...

So those would be the columns in the dataset. Not at all pretty. At all.

[dc7669]

@Greg_Deckler Your understanding is absolutely correct.  In essense, I have a data table with 41 fields (13+7+10+10) grouped into 4 groups.  About Filtering on Unpivoted table, it seemed that filtering cannot be done before Unpivot.  This probably makes sense.  

I tried @v-gizhi-msft's suggestion on making 4 copies of the table and then unpivot one group of fields at a time.  It worked.  And then, I created "Many-to-Many" relationship to link the 4 tables together.  Then, I tried to do 4 slicers.  I saw that if Person A has mutliple attributes within the same group, Person A would appear multiple times when none of the group's attribute is selected in a slicer.  This does not occur if I were to unpivot 4 times on one single table.

Thanks @Greg_Deckler @v-gizhi-msft for your comments and inputs.   

[v-gizhi-msft]

Hi, 

@Greg_Deckler 

I think i can select all columns under one specified attribute to reserve and as a new table, continue steps and generate four tables at last, then unpivot all these columns for each table.

Best Regards,

Giotto

[Greg_Deckler]

Yes, but you would still need all the columns of attribute 1 in a single column to make a relationship. I don't know, it's very ugly, maybe @ImkeF has an idea or @edhans .

[ImkeF]

Hi @dc7669 ,

Could it be that you need 4 different (dimension) tables instead?

[edhans]

@dc7669 

I tend to agree with @ImkeF 's thoughts here. I'd step back and look at what is in that table. It might need to be for DIM tables, or we could split it into 4 queries in Power Query, then re-merge them as one if it is really one set of data that is just horribly organized at the source. 

[dc7669]

From what I read, dimensional tables has all the facts (or fields that I want to slice/dice with).   In my example, it will be 4 groups of attributes.  Each group of attributes have about 10 possible options.  Each option is simply yes/no.  So, each person in the table has 40 fields that has yes/no in it.   I have about 100 persons in the table.  So, 5 dimension (the person field +  4 group of attribute) table I will have 100 x 10 x 10 x 10 x 10 = 1,000,000 rows.  That is exactly what Unpivot the table 4 times will give me.  So, I doubt this strategy has any advantage.   

The presentation I want to make is for user to select all or one of the option of the 4 groups of attributes and the user will see all the persons who met the selected criteria only once. 

May be there is really no easy way out of this. 

Thanks for all your inputs. 

dc7669

[ImkeF]

Hi @dc7669 ,

what I particularly don't understand is this: "and the user will see all the persons who met the selected criteria only once. "

How about creating some real mockup data with 2 users, 2 attributes with 2 options each and create some pictures how you want the report to behave?

[dc7669]

Hi,

Here is the result.  

I have 4 Service Areas, 3 License Types and 3 Services.  With 3 companies, unpivot 3 times, the total number of rows increased from 3 to 108 (4x3x3x3).  

This is the PBIX file.  

[ImkeF]

Sorry, but still failing to understand the business logic/ desired report logic here.

Could you please try to describe with screenshots what you're trying to accomplish?

[dc7669]

I have a dataset like the above. 

There 4 fields for Service Area, 3 fields for licenses, and 3 fields for services.

I want to create report such that user can query one of fields in either/combined Service Area, license and service like below.

The question is in this simple example.  I only have 3 companies, but by unpivoting 3 times, once for Service Area, license and service.  This 3 rows entries becomes 108 row entries.   This seemed to take a lot computing time to do.  I wonder if there is a less computer intensive way to accomplish this other than unpivoting.  

[ImkeF]

Ok, so I previously thought that you were looking for a way to avoid the large number of rows.

But with regards to the unpivoting, there is an easier (an probably faster) way:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLUjMq3RU0lGKAGIohRvH6sD1OGFRAEcwDpJ6Z1QZDI1QC2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CompanyName = _t, ServiceArea_N = _t, ServiceArea_E = _t, ServiceArea_S = _t, ServiceArea_W = _t, License_Plumbing = _t, License_Electrical = _t, License_GeneralContractor = _t, Service_Landscaping = _t, Service_MajorRenovation = _t, Service_MinorREnovation = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CompanyName", type text}, {"ServiceArea_N", type text}, {"ServiceArea_E", type text}, {"ServiceArea_S", type text}, {"ServiceArea_W", type text}, {"License_Plumbing", type text}, {"License_Electrical", type text}, {"License_GeneralContractor", type text}, {"Service_Landscaping", type text}, {"Service_MajorRenovation", type text}, {"Service_MinorREnovation", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"CompanyName"}, "Attribute", "Value"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Other Columns", "Attribute", "Attribute - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Attribute - Copy", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute - Copy.1", "Attribute - Copy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute - Copy.1", type text}, {"Attribute - Copy.2", type text}}),
    #"Grouped Rows1" = Table.Group(#"Changed Type1", {"CompanyName"}, {{"All", each _, type table [CompanyName=text, Attribute=text, Value=text, #"Attribute - Copy.1"=text, #"Attribute - Copy.2"=text]}}, GroupKind.Local),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Grouped Rows1", "All", "All - Copy"),
    #"Expanded All - Copy" = Table.ExpandTableColumn(#"Duplicated Column1", "All - Copy", {"Value", "Attribute - Copy.1"}, {"License_Value", "License_Attribute"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded All - Copy", each ([License_Attribute] = "License")),
    #"Duplicated Column2" = Table.DuplicateColumn(#"Filtered Rows1", "All", "All - Copy"),
    #"Expanded All - Copy1" = Table.ExpandTableColumn(#"Duplicated Column2", "All - Copy", {"Value", "Attribute - Copy.1"}, {"Service_Value", "Service_Attribute"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded All - Copy1", each ([Service_Attribute] = "Service")),
    #"Duplicated Column3" = Table.DuplicateColumn(#"Filtered Rows", "All", "All - Copy"),
    #"Expanded All - Copy2" = Table.ExpandTableColumn(#"Duplicated Column3", "All - Copy", {"Value", "Attribute - Copy.1"}, {"ServiceArea_Value", "ServiceArea_Attribute"}),
    #"Filtered Rows2" = Table.SelectRows(#"Expanded All - Copy2", each ([ServiceArea_Attribute] = "ServiceArea")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows2", "Service_All", each Text.Combine( Table.SelectRows([All], (x) => x[#"Attribute - Copy.1"] = "Service")[#"Attribute - Copy.2"], "#(lf)"))
in
    #"Added Custom"

 ... I'll leave it to you to create the last 2 "All-Fields" according to the pattern.

BTW: You'd better create the "All-columns" first before starting to expand out the others.

[Greg_Deckler]

Well yes, but wouldn't you just filter the table for where all Value columns were blank?