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 😉

View solution in original post

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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors