Expand the row

[Shee_powerbi]

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
wers@outisf.com			AR
weyu@outisf.com	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
wers@outisf.com	ERGT	TGHY	AR	ERGT
wers@outisf.com	ERWS	REFD	AR	ERFG
wers@outisf.com	WESV	ESW	AR	ERGT
weyu@outisf.com	WERF	RTGF	AU	WEFT
weyu@outisf.com	WERF	RTGF	NZ	WERI

[Shee_powerbi]

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

[Shee_powerbi]

Actually after 4th step to add a new column. expand icon on new column is not appearing for me.

[v-yueyunzh-msft]

Hi , @Shee_powerbi 

The added column should return a table type like this:

The returned result in your side is not a table? If it is a table , it will have the expand icon:

You can see my test .pbix file detailed steps .

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

[Shee_powerbi]

Hey, thanks for the quick response. I am getting the same error while I am trying to expand the columns first.

[v-yueyunzh-msft · Accepted solution]

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
    Custom1

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

[vicky_]

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