Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
i have recods in excel like
company region market rev1India revgermany revsingapore
EAC Sols APAC Germany 44682 - 12455
Now i want in power bi to look like
Company Region Market Country Rev
EAC sols APAC Germany India 44682
EAC sols APAC Germany Germany -
EAC sols APAC Germaby Singapore 12455
So basically i need 2 things
create rows 3 rows for every record for india,germany and singapore and then put the revenue values against it,
Help!!
Solved! Go to Solution.
Hi @Anonymous ,
On the query editor select the 3 columns of Rev and then choose unpivot. You will then get 2 column attribute and value.
You then just have to rename the columns and on the attribute column make a replace rev by nothing and you you get the information needed.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnV0VgjOzylW0lFyDHB0BlLuqUW5iXmVQJaJiZmFEZA2AGJDIxNTU6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company = _t, Region = _t, Market = _t, revIndia = _t, Revgermany = _t, revsingapore = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"Region", type text}, {"Market", type text}, {"revIndia", Int64.Type}, {"Revgermany", Int64.Type}, {"revsingapore", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Company", "Region", "Market"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Country"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","rev","",Replacer.ReplaceText,{"Country"})
in
#"Replaced Value"
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous ,
If you want to implement it with DAX, you could try this:
Create a new table:
Table 1 =
UNION (
SELECTCOLUMNS (
'Table',
"Company", 'Table'[Company],
"Region", 'Table'[Region],
"Market", 'Table'[Market],
"Country", "India",
"Rev", 'Table'[revIndia]
),
SELECTCOLUMNS (
'Table',
"Company", 'Table'[Company],
"Region", 'Table'[Region],
"Market", 'Table'[Market],
"Country", "Germany",
"Rev", 'Table'[Revgermany]
),
SELECTCOLUMNS (
'Table',
"Company", 'Table'[Company],
"Region", 'Table'[Region],
"Market", 'Table'[Market],
"Country", "Singapore",
"Rev", 'Table'[revsingapore]
)
)
Hi @Anonymous ,
If you want to implement it with DAX, you could try this:
Create a new table:
Table 1 =
UNION (
SELECTCOLUMNS (
'Table',
"Company", 'Table'[Company],
"Region", 'Table'[Region],
"Market", 'Table'[Market],
"Country", "India",
"Rev", 'Table'[revIndia]
),
SELECTCOLUMNS (
'Table',
"Company", 'Table'[Company],
"Region", 'Table'[Region],
"Market", 'Table'[Market],
"Country", "Germany",
"Rev", 'Table'[Revgermany]
),
SELECTCOLUMNS (
'Table',
"Company", 'Table'[Company],
"Region", 'Table'[Region],
"Market", 'Table'[Market],
"Country", "Singapore",
"Rev", 'Table'[revsingapore]
)
)
@Anonymous , select the last three columns and unpivot
https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/
Hi @Anonymous ,
On the query editor select the 3 columns of Rev and then choose unpivot. You will then get 2 column attribute and value.
You then just have to rename the columns and on the attribute column make a replace rev by nothing and you you get the information needed.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnV0VgjOzylW0lFyDHB0BlLuqUW5iXmVQJaJiZmFEZA2AGJDIxNTU6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company = _t, Region = _t, Market = _t, revIndia = _t, Revgermany = _t, revsingapore = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"Region", type text}, {"Market", type text}, {"revIndia", Int64.Type}, {"Revgermany", Int64.Type}, {"revsingapore", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Company", "Region", "Market"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Country"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","rev","",Replacer.ReplaceText,{"Country"})
in
#"Replaced Value"
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@Anonymous - Select your last three columns in Power Query editor and then right-click in the column header area and select Unpivot.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |