Forum Discussion

arthur_mq's avatar
arthur_mq
Frequent Visitor
2 years ago

Rearrange table by grouping values in columns

Hi, I have a table that list items by date (ItemsTable below). Each date lists more than one item. I want to rearrange this table by grouping the dates in columns with their items listed below (Ite...
Need Help
arthur_mq's avatar
arthur_mq
Frequent Visitor
2 years ago

gmsamborn  I think this could be the solution. Is there a way to number each item and sort them in ascending order and also two spaces between each item? 

gmsamborn's avatar
gmsamborn
Super User
2 years ago

Hi arthur_mq 

 

I'm not sure how to "number each item" but sorting is handled with CONCATENATEX's optional parameters.  Can you explain this numbering?

 

(I would probably wait to see what Ashish_Mathur  can come up with using a Power Query approach since this CONCATENATEX() usage is very limited and mainly for display purposes.)

 

  • arthur_mq's avatar
    arthur_mq
    Frequent Visitor
    2 years ago

    Hi gmsamborn 

    The items are long text sentences, and I need to asign a number to each item and sort them like this:

     

    1) Establish Appropriations, Advertise and Award Construction Contract for Pedestrian Safety           Improvements at Four Intersections in the Communities.

     

    2) Ordinance Amendment to the Grading, Clearing and Watercourses Ordinance to Allow By-          Right Ministerial Grading Permits for By-Right Housing Projects

     

    3) Establish Appropriations, Advertise and Award a Construction Contract for Pedestrian                  Crossing Enhancements at 17 Locations.

  • arthur_mq's avatar
    arthur_mq
    Frequent Visitor
    2 years ago

    Hi gmsamborn 

    I modified the DAX formula to add a number to each item and sort them, but the sorting is not working. Any ideas?

     

     

    Vertical List_M =
    CONCATENATEX(
    'Table',
    " (" & FORMAT(RANKX('Table', 'Table'[Item],,ASC), "0") & ") "& 'Table'[Item] & UNICHAR(10) & UNICHAR(10)
    )

    • gmsamborn's avatar
      gmsamborn
      Super User
      2 years ago

      Hi arthur_mq 

       

      Would something like this help?

      Vertical List_M =
CONCATENATEX(
    'Table',
    " ("
        & FORMAT(
            RANKX(
                'Table',
                'Table'[Item],
                ,
                ASC
            ),
            "0"
        ) & ") " & 'Table'[Item],
	UNICHAR( 10 ),		-- Separator (otpional)
	,					-- Sorting expression (optional)
	ASC					-- Sorting direction (optional)
)

       

      I'm not sure that will work and am pretty much out of ideas.   Like I mentioned earlier, this method has limited usefulness.