take the values from second rows and list them in a different column using DAX

[Anonymous]

Hi fellows,

I was helping a coworker while she brought up a question. I will demonstrate it as follows:

A	SalesValue	Address
12346548 Task1	1597823$	St.1 street
326597 Task2	3258$	St.3 street
657415 Task3	25478 $	St.2 street

And she wants this table to look like:

A	Task	SalesValue	Address
12346548	1	1597823$	St.1street
326597	2	3258$	St.3street
657415	3	25478$	St.2street

Basically, she tries to add a column and pull all the second lind values (inside same cells not a new row) and list them under a new column with the assigned name like above one.

Note: The preference is using power bi design page without going to Power Query Editor  page.

Thanks

[Anonymous · Accepted solution]

Actually it is so easy, Go to :

Format -> Row headers -> Stepped layout 

and turn it "Off" 

That simple and easy without going over huge Power Query massaging and scripting 😉

[mahoneypat]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.  Note that I added a step up front to replace blanks with nulls, which is necessary for this to work.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XctNCoAgEIDRq4i0lGD+1M5RO3HRwlW79P6kYWDCwAzM90LQgMRW2Guj20QT2gbZnEda6rmXFVQud0rl/R5nvmCoCW2N/5xQfLc0W1RDa8UxyB+jsPOqc5w5fXF8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, SalesValue = _t, Address = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"SalesValue", Currency.Type}, {"Address", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"A", "Address"}),
    #"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "ForUnPivot", each Number.RoundUp([Index]/3,0), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"ForUnPivot"}, "Attribute", "Value"),
    Custom1 = Table.FromColumns({#"Unpivoted Other Columns"[ForUnPivot], #"Unpivoted Other Columns"[Value], List.Repeat({"A", "SalesValue", "Address", "Task"}, Table.RowCount(#"Unpivoted Other Columns")/4)}),
    #"Pivoted Column" = Table.Pivot(Custom1, List.Distinct(Custom1[Column3]), "Column3", "Column2")
in
    #"Pivoted Column"

Pat

[Anonymous]

Thanks! Is it possible to do it in DAX or any other way except using Power Editor?

[mahoneypat]

I missed that part of your initial post.  This would be very difficult or may not be possible in DAX.

Pat