Forum Discussion
Expand the row
Hi, I have an excel file with roles of the employee. Expanded data is in SAP HANA. I need to expand the data in excel to create a unique key and match the data with SAP HANA.
Data in excel -
| Email id | Market | Region | Country | Company |
| [email protected] | AR | |||
| [email protected] | WERF | RTGF |
SAP HANA table
| Market | Region | Country | Company |
| ERGT | TGHY | AR | ERGT |
| ERWS | REFD | AR | ERFG |
| WESV | ESW | AR | ERGT |
| WERF | RTGF | AU | WEFT |
| WERF | RTGF | NZ | WERI |
Post expansion resultant table should be like -
| Email id | Market | Region | Country | Company |
| [email protected] | ERGT | TGHY | AR | ERGT |
| [email protected] | ERWS | REFD | AR | ERFG |
| [email protected] | WESV | ESW | AR | ERGT |
| [email protected] | WERF | RTGF | AU | WEFT |
| [email protected] | WERF | RTGF | NZ | WERI |
Hi, Shee_powerbi
According to your description, you want to expand the rows in Excel from the table in SAP HANA table . Right?
Here are the steps you can refer to :
(1)My test data is the same as yours.(2)We can unpivot the Table in Excel ,like this:
(3)Then we can filter the null rows:
(4)Then we can add a custom column:
(x)=> if x[Attribute] = "Country" then Table.SelectRows(#"Table 2",(y)=>y[Country]=x[Value]) else if x[Attribute] = "Market" then Table.SelectRows(#"Table 2",(y)=>y[Market]=x[Value]) else if x[Attribute] = "Region" then Table.SelectRows(#"Table 2",(y)=>y[Region]=x[Value]) else if x[Attribute] = "Company" then Table.SelectRows(#"Table 2",(y)=>y[Company]=x[Value]) else null(5)Then we can remove the columns wo do not need and expand the columns, in the end we can get the distinct Table we want to :
So , you can put this code in the "Advanced Editor" to refer to :
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKk8tKnbILy3JLE7TS87PVdJRUoBixyAwI1YHpKiyFFVRuGuQG5AKCnF3g2uJjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Email id" = _t, Market = _t, Region = _t, Country = _t, Company = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Email id", type text}, {"Market", type text}, {"Region", type text}, {"Country", type text}, {"Company", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Email id"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> " ")), #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", (x)=> if x[Attribute] = "Country" then Table.SelectRows(#"Table 2",(y)=>y[Country]=x[Value]) else if x[Attribute] = "Market" then Table.SelectRows(#"Table 2",(y)=>y[Market]=x[Value]) else if x[Attribute] = "Region" then Table.SelectRows(#"Table 2",(y)=>y[Region]=x[Value]) else if x[Attribute] = "Company" then Table.SelectRows(#"Table 2",(y)=>y[Company]=x[Value]) else null ), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute", "Value"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Market", "Region", "Country", "Company"}, {"Market", "Region", "Country", "Company"}), Custom1 = Table.Distinct(#"Expanded Custom") in Custom1Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
7 Replies
vicky_Super User
At the moment, I don't see any clear relationship between the two tables. But if you have a primary key, you can use the Merge Queries function in Power Query to merge and expand the two tables. Here's the docs to help you get started: https://learn.microsoft.com/en-us/power-query/merge-queries-overview
- v-yueyunzh-msft
Community Support
Hi, Shee_powerbi
According to your description, you want to expand the rows in Excel from the table in SAP HANA table . Right?
Here are the steps you can refer to :
(1)My test data is the same as yours.(2)We can unpivot the Table in Excel ,like this:
(3)Then we can filter the null rows:
(4)Then we can add a custom column:
(x)=> if x[Attribute] = "Country" then Table.SelectRows(#"Table 2",(y)=>y[Country]=x[Value]) else if x[Attribute] = "Market" then Table.SelectRows(#"Table 2",(y)=>y[Market]=x[Value]) else if x[Attribute] = "Region" then Table.SelectRows(#"Table 2",(y)=>y[Region]=x[Value]) else if x[Attribute] = "Company" then Table.SelectRows(#"Table 2",(y)=>y[Company]=x[Value]) else null(5)Then we can remove the columns wo do not need and expand the columns, in the end we can get the distinct Table we want to :
So , you can put this code in the "Advanced Editor" to refer to :
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKk8tKnbILy3JLE7TS87PVdJRUoBixyAwI1YHpKiyFFVRuGuQG5AKCnF3g2uJjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Email id" = _t, Market = _t, Region = _t, Country = _t, Company = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Email id", type text}, {"Market", type text}, {"Region", type text}, {"Country", type text}, {"Company", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Email id"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> " ")), #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", (x)=> if x[Attribute] = "Country" then Table.SelectRows(#"Table 2",(y)=>y[Country]=x[Value]) else if x[Attribute] = "Market" then Table.SelectRows(#"Table 2",(y)=>y[Market]=x[Value]) else if x[Attribute] = "Region" then Table.SelectRows(#"Table 2",(y)=>y[Region]=x[Value]) else if x[Attribute] = "Company" then Table.SelectRows(#"Table 2",(y)=>y[Company]=x[Value]) else null ), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute", "Value"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Market", "Region", "Country", "Company"}, {"Market", "Region", "Country", "Company"}), Custom1 = Table.Distinct(#"Expanded Custom") in Custom1Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi, Thank you for your solution. After adding the new column, when I am trying to remove unwanted columns I am getting Expression.Error: A cyclic reference was encountered during evaluation.
errror. Any reason for this?
- v-yueyunzh-msft
Hi , Shee_powerbi
Can you show what the columns and where you remove the columns like a scrennshot?
Or can you first expand the table and in the end then remove columns?
The error code means that this step depends on this column, so there is a circular dependency.
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hey, thanks for the quick response. I am getting the same error while I am trying to expand the columns first.
