Forum Discussion
Need help with a XLOOKUP Table Visual
Hi all,
I have a data source, where there are about 20 columns and hundreds of row of data. I am trying to create a table visual with text filter where my 1st column is fixed with the different countries, and the 2nd, 3rd and 4th columns values would be done as a XLOOKUP (in excel).
Please see below representative example, and really appreciate any help here.
Data Source
| ID_Number | US Revenue | US Profits | EU Revenue | EU Profits |
| 200 | 1,000,000 | 115,000 | 245,500 | 144,000 |
| 201 | 2,500,000 | 500,000 | 750,123 | 485,202 |
| 203... |
Ideal Table Visual on PowerBI
(Users will type in a text filter where they enter the ID_Number, and the results in the table below will reflect accordingly)
For example: ID_Number is 201
| Country | Revenue | Profits |
| US | 1,000,000 | 115,000 |
| EU | 245,500 | 485,202 |
Thank you all!
cfoo , Better to unpivot the table in the power query and then split the attribute column by delimiters
Use this code in the blank query in power query and check the steps
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PYqxDcAwDMN+8ZxBViwkvwT+/43GNVpNBMVzjIANc7wrcjUwpDYRZXJU7PXoi39YgnNeiC2ClvkA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID_Number = _t, #"US Revenue" = _t, #"US Profits" = _t, #"EU Revenue" = _t, #"EU Profits" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID_Number", Int64.Type}, {"US Revenue", Int64.Type}, {"US Profits", Int64.Type}, {"EU Revenue", Int64.Type}, {"EU Profits", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID_Number"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}) in #"Changed Type1"Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g
Split column and Combine Columns: https://youtu.be/T30H_fe2uAA
Blog and Link of file - https://medium.com/chandakamit/split-column-and-combine-columns-c774f49b7218Now use Matrix Visual
2 Replies
- amitchandak
Super User
cfoo , Better to unpivot the table in the power query and then split the attribute column by delimiters
Use this code in the blank query in power query and check the steps
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PYqxDcAwDMN+8ZxBViwkvwT+/43GNVpNBMVzjIANc7wrcjUwpDYRZXJU7PXoi39YgnNeiC2ClvkA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID_Number = _t, #"US Revenue" = _t, #"US Profits" = _t, #"EU Revenue" = _t, #"EU Profits" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID_Number", Int64.Type}, {"US Revenue", Int64.Type}, {"US Profits", Int64.Type}, {"EU Revenue", Int64.Type}, {"EU Profits", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID_Number"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}) in #"Changed Type1"Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g
Split column and Combine Columns: https://youtu.be/T30H_fe2uAA
Blog and Link of file - https://medium.com/chandakamit/split-column-and-combine-columns-c774f49b7218Now use Matrix Visual
This is good Amit, i managed to create something with your advice. thank you!
