Is there a way to turn a matrix into a list?

[Anonymous]

I have two matrixes that are updated into excel and then I have to convert into list of unique permutations in power BI. Right now I am handling all that manually by pivoting it in excel and finagling it.

Is there a way to use table creation or power query to help streamline this process.

ex.

Matrix 1:

      1 |  2  

A: A1 | A2 

B: B1 | B2 

Matrix 2:

        1 | 2 

C : C1  | C2 

D : D1 | D2

Output:

1. A1 C1

2: A1 D1

3: A1 C2

4: A1 D2

5: A2 C1

.... and so on, until all permutations of both tables are exhausted.

So is there away to feed Power BI the two matrixes and configure Power Query or through DAX to create the unique permutations of both?

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

If your data shows as below when importing into Power BI

1. PromoteHeaders for two tables

2. add a custom column in Table 1

Custom=Table2

Expand all values

3. add merged columns

select [1], [Custom.1] to merge columns->Merged1

select [1], [Custom.2] to merge columns->Merged2

select [2], [Custom.1] to merge columns->Merged3

select [2], [Custom.2] to merge columns->Merged4

4.

remove columns "", "1", "2", "Custom.", "Custom.1", "Custom.2"

Create four statements in Advanced editor

Finally, append tables with the code

#"table append"=Table.Combine({#"select column1", #"select column2",#"select column3",#"select column4"})

You could open my file and check the steps one by one.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

If your data shows as below when importing into Power BI

1. PromoteHeaders for two tables

2. add a custom column in Table 1

Custom=Table2

Expand all values

3. add merged columns

select [1], [Custom.1] to merge columns->Merged1

select [1], [Custom.2] to merge columns->Merged2

select [2], [Custom.1] to merge columns->Merged3

select [2], [Custom.2] to merge columns->Merged4

4.

remove columns "", "1", "2", "Custom.", "Custom.1", "Custom.2"

Create four statements in Advanced editor

Finally, append tables with the code

#"table append"=Table.Combine({#"select column1", #"select column2",#"select column3",#"select column4"})

You could open my file and check the steps one by one.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ImkeF · Accepted solution]

Hi @Anonymous ,

a dynamic approach that works on tables of any number of columns out of the box would be this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRMgRiI6VYnWglRyDLEcR1hPCdgEwnEN8JyI8FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"1", type text}, {"2", type text}}),
    Values1 = List.Combine(Table.ToRows(#"Changed Type1")),
    Values2 = List.Combine(Table.ToRows(Table2)),
    #"Converted to Table" = Table.FromList(Values1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Values2),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

Just create a new query in @v-juanli-msft  s file and see how it works.

[Cmcmahan]

Sure.  If you just have tables that list each of the options, this can be done with CROSSJOIN.  Table 1 would be {A1, B1, A2, B2}.  Table 2 would be {C1, D1, D2, C2}.  With DAX, you could use this expression:

Output1 = CROSSJOIN('Table 1', 'Table 2')

However, it doesn't come with an index, and creating one is a hassle involving RANKX and whatnot.  This seems like a better job for power query anyway. 

Using this expression, I was able to create the same table, but with an index column:

let
    #"Crossjoin" = Table.Join(Table.AddColumn(#"Table 1", "JoinVal", each 1), "JoinVal", Table.AddColumn(#"Table 2", "JoinVal", each 1), "JoinVal"),
    #"Removed Columns" = Table.RemoveColumns(Crossjoin,{"JoinVal"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1)
in
    #"Added Index"

The trick is to add a column (called JoinVal) that has the same value for each row (1) on both tables, and then do an inner join based on where those columns are the same.  It's a bit of a hack, but gets the job done.

[Anonymous]

This helps, but I am still missing turning the original two matrices into lists. Is there a way to do that?

[Cmcmahan]

That's probably best to fix in your original data source.   If that's not possible, you can Unpivot the tables you have and remove the resulting Attribute column, leaving only the Col1 column.

Table.UnpivotOtherColumns(#"Table 1", {}, "Attribute", "Col1")