Use table to create a new table

[Elsie14]

Hi guys,

I am quite new to Power BI so I think my problem is a piece of cake to the experienced Power BI user. I have a excel datasheet uploaded in power BI. In this dataset I have several colums. For my problem only a few of these columns are used. In this case I have a set of users which are allocated to a specific country. Each user has a specific license to a product. These licenses are columns as well and when a person has a specific license it is marked in the license column as TRUE. For example:

Name                      Country                      License A                       License B                   License C

Tom Jones               US                                TRUE                     

Catherine Smith      CA                                                                                                          TRUE

Phil Aitken               GB                                TRUE

Now what I want is a table that counts the number of licenses per country. So like this:

Country              License A                License B                  License C

US                       1                               0                                0

CA                       0                               0                                1

GB                       1                               0                                0

How can I convert/alter the table to get this second table?

Thank you!

[AlexChen]

Hi,

I create a table call “license” using your data. See screenshot below.

You can create a new table using code below.

Table = SUMMARIZE(license,
                license[country],
                "licenseA", if(COUNTAX(license, license[licenseA]) = BLANK(), 0, COUNTAX(license, license[licenseA])),
                "licenseB", if(COUNTAX(license, license[licenseB]) = BLANK(), 0, COUNTAX(license, license[licenseB])),
                "licenseC", if(COUNTAX(license, license[licenseC]) = BLANK(), 0, COUNTAX(license, license[licenseC]))
                )

And this is the result you want:

Best Regards

Alex

[KGrice]

If you're doing this in the Query Editor, starting with your original table: Select your license columns, then in the Transform tab change the Data Type to Whole Number. That should convert all of your TRUEs to 1s. Then select the Country column and click Group By in the Transform tab. Use the settings below:

Once you hit OK, you'll have your end table.

You could also show this calculation and end result as a table visual in the report, without changing the underlying table itself. Just Close and Apply your original table, drag the Country field out onto the report, and change the visual to a table in the Visualizations pane. Then define 3 new measures. To create the first, click the New Measure button on the Modeling tab and use:

License A Count = COUNTA(TableName[LicenseA])

The name of the measure is up to you. Repeat for License B, C, etc. Once your measures are created, you can drag them onto your table visual as well, or drag them into the Values section in the Visualizations pane while your table is selected on the report.

You could also do this without explicitly defining measures. To see what I mean, drag the License A column (not the measure) onto the table. By default, it won't aggregate, and should show several True values on the table. You can click the drop-down on the field name in the Values section, and select Count instead of Don't summarize. The downside is you'd have to do this every time you dropped this onto a visual. By creating the measures, you only have to do it once and you can use them anywhere, including other formulas.

[Elsie14]

All methods worked perfectly. Thanks a lot!