Calculate one table into multiples based on first character(s) in a column

[Anonymous]

Been searching for hours and have not found a case that is close enough to what I am trying to do to help this PBI "noob."

I have a main data table (called "RAW") imported in Power BI. All fine, but I need to filter it into several other tables based on the starting characters of a few different columns. For most, a [Project Id} column is a 10-digit string that may start with a P,W, E, R, or a few others.

So for example, in one case Table1 = the entire RAW table, but only bringing over rows where the string in [Project Id} begins with a "W." 

There are a few cases where I need to AND together conditions from several columns, but in every case it's a "begins with" scenario (as opposed to "contains").

I am going down the path of CALCULATETABLE, but having trouble setting the filter conditions. Would this use a LEFT, and if so, how do you incorporate that into a CALCULATETABLE?

[Anonymous]

HI @Anonymous,

If you can please share some sample data with expected result to help use clarify your requirement. It is hard to coding formula based on your description.

Regards,

Xiaoxin Sheng

[Anonymous]

Since my data is huge I will use a highly-simplified example.

Say I start with a table named RAW that has these columns/rows:

A             B          C         D

P12345   39.42   1864    Pjlxt

R12345   89.32   54a3    Dlvt

P98765   75.32   09r5    Lsd.t

From this I would create two additional tables based on the first character in column A, so one table would look like this (forst character "P"):

A             B          C         D

P12345   39.42   1864    Pjlxt

P98765   75.32   09r5    Lsd.t

And the other would look like this (first character "R"):

A             B          C         D

R12345   89.32   54a3    Dlvt

[Anonymous]

HI @Anonymous,

Unfortunately, current both dax and m query not contains functions to generate multiple tables based on one table. You had to manually create these tables based on source data.

In my opinion, I'd like to suggest add calculated column to store first character, then use this data to create matrix table.(new column and column A to rows field, other column to value field)

Header =
LEFT ( [A], 1 )

Regards,

Xiaoxin Sheng