Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |