Create 3 rows for every record

[Anonymous]

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!! 

[MFelix · Accepted 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"

[v-xuding-msft · Accepted solution]

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]
    )
)

[amitchandak]

@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/

[MFelix · Accepted 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"

[Greg_Deckler]

@Anonymous - Select your last three columns in Power Query editor and then right-click in the column header area and select Unpivot.