Expand the row

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