cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bestmbaman
Frequent Visitor

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

Hi fellows,

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

ASalesValueAddress

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:

ATaskSalesValueAddress
123465481

1597823$

St.1street

3265972

3258$

St.3street

6574153

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

1 ACCEPTED SOLUTION
bestmbaman
Frequent Visitor

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 😉

View solution in original post

4 REPLIES 4
bestmbaman
Frequent Visitor

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
Super User
Super User

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

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

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!