How do I do this.....?

[cruzrji1]

Hello guys,

Im new wiht the PowerBI tools and trying to keep on...

Also, I dont know how to call what I need to do even in spanish (my native language) so I´ll give an example

Before that, I'd like to state that this is a real world problem for me, Im working in a large construction project and this example is an over simplification of my problem, so any help will be very appreciated!

=)

I have three tables kind of sources...

Table 1:

LVL 1	Description LvL 1
A1	Civil
A2	Mecanica
A3	Electricidad

<tbody>
</tbody>

Table 2: LvL 2 Description LvL 2 B1 Cimentaciones B2 Muros B3 Compresores B4 Reactores B5 Cableado B6 Transformadores <tbody> </tbody>

<tbody>
</tbody>

Table 3 :

LvL 1	LvL 2
A1	B1
A1	B2
A2	B3
A2	B4
A3	B5
A3	B6

<tbody>
</tbody>

Then I have a fourth table :

Table 4:

LvL 1

A1

A1

A3

A2

A3

A2

A3

<tbody>
</tbody>

How do I get this result from table 4?:

LVL 1	Description LvL 1	LvL 2	Description LvL 2
A1	Civil	B1	Cimentaciones
A1	Civil	B2	Muros
A1	Civil	B1	Cimentaciones
A1	Civil	B2	Muros
A3	Electricidad	B5	Cableado
A3	Electricidad	B6	Transformadores
A2	Mecanica	B3	Compresores
A2	Mecanica	B4	Reactores
A3	Electricidad	B5	Cableado
A3	Electricidad	B6	Transformadores
A2	Mecanica	B3	Compresores
A2	Mecanica	B4	Reactores
A3	Electricidad	B5	Cableado
A3	Electricidad	B6	Transformadores

<tbody>
</tbody>

I filled the last table manually, of course I could use a lot of vlookups or some vba or something like that, but I Know there is a simpler way using Power Tools, maybe PowerQuery or PowerPivot I dont know...

Please Help!

 

[NotoriousPopol]

A suggestion. On your 4th table, you could go with:

Col A: Table3!ColA
Col B=Vlookup(Col A,Table1!B:B,1,FALSE)
Col C: Table3!ColC
Col D=Vlookup(Col A,Table1!B:B,1,FALSE)

I think this is an easy way to do that. If you create a Table, formulas will extend automatically. That's one formule for each columns, pretty light!

 

[Comfy]

Try this:

let
Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lFyzizLzFGK1QFyjYBc39TkxLzM5ESIiDFQxDUnNbmkKDM5MyUxRSk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t]),
Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjJU0lFyzsxNzStJTM7Mz0stVorVAQobAYV9S4vyoVxjkKr83IKi1OL8IpgaE6BgUGpicglCyBSkLjEpJzUxJR8iYgYUCSlKzCtOyy/KBYqClcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t]),
Table3 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lFyMlSK1YGxjSBsIxDbGIltAmEbg9imSGwzpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t]),
Table4 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRUitVBUMYQyggHLxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
MergeTbl3 = Table.NestedJoin(Table4,{"Column1"},Table3,{"Col1"},"NewColumn",JoinKind.LeftOuter),
ShowRelation = Table.ExpandTableColumn(MergeTbl3, "NewColumn", {"Col2"}, {"NewColumn.Col2"}),
MergeTbl1 = Table.NestedJoin(ShowRelation,{"Column1"},Table1,{"Col1"},"NewColumn",JoinKind.LeftOuter),
ShowTbl1Desc = Table.ExpandTableColumn(MergeTbl1, "NewColumn", {"Col2"}, {"NewColumn.Col2.1"}),
MergeTbl2 = Table.NestedJoin(ShowTbl1Desc,{"NewColumn.Col2"},Table2,{"Col1"},"NewColumn",JoinKind.LeftOuter),
ShowTbl2Desc = Table.ExpandTableColumn(MergeTbl2, "NewColumn", {"Col2"}, {"NewColumn.Col2.2"}),
#"Reordered Columns" = Table.ReorderColumns(ShowTbl2Desc,{"Column1", "NewColumn.Col2.1", "NewColumn.Col2", "NewColumn.Col2.2"})
in
#"Reordered Columns"

Please note the duplicates because of Table 3 and Table 4.

 

[cruzrji1]

Thanks for the answer, I´m guessing that code must be on power query, now I only have to learn how to use it...

 

[Comfy]

In Power Query, either in Excel or Power BI Desktop, open the advanced editor and paste the code above.

This was only an example. You will have to change the data source of Table1, Table2 and Table3.