Convert from Column to Row.

[tiwari_456]

Hello Mentors,
I am stuck in a data problem, so My scenario is that my source of data is a SharePoint list collection data from any form. Where I have a table named "tblCMP" which consists of 50 + columns for example and replicating the table below in which I have columns like "line1", "line2" and "line3" and dates associated with them like "Date line1", "Date line2" and "Date line3", few other columns as well like "title of change" and  "comments" so it could be a scenario that if a new record gets added it may have detail on "line1" and "Date line1"  but rest of the two columns for line 2 and line 3 could be empty or in other cases and even an empty and all three can be filled so I have to make a Gantt chart over it and combining these columns.

tblCMP

ID	Title of Change	Line1	Date Line1	Line2	Date Line2	Line3	Date Line3	Comments
1	TestCase1	Complete	4/1/2024	NA	NA	NA	NA	TestComment1
2	TestCase2	Incomplete	4/2/2024	Discard	4/1/2024	Complete	4/3/2024	TestComment2
3	TestCase3	NA	NA	Incomplete	4/3/2024	NA	NA	TestComment3
4	TestCase4	Complete	4/4/2024	NA	NA	Incomplete	4/3/2024	TestComment2

Now I have to make a Gantt Chart out of it, Please assist me with, how I can combine these Lines and their dates to show them all together.

[TheoC]

Hi @tiwari_456 

If I understand correctly, you can achieve this by going into Power Query and unpivoting the Date, Line 1, Line 2, and Line 3.

All you need to do in Power Query is select the columns (hold Ctrl button and click on the columns), then right click, and click on Unpivot.  It should work subject to your data structure being appropriate.

Let me know how this goes!

Theo

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="tblCMP"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID", "Title of Change", "Comments"}, "Attribute", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute.1", "Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Character Transition", List.Distinct(#"Split Column by Character Transition"[Attribute.1]), "Attribute.1", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Attribute.2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","NA","",Replacer.ReplaceValue,{"Line", "Date Line"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"ID", type text}, {"Title of Change", type text}, {"Comments", type text}, {"Line", type text}, {"Date Line", type datetime}})
in
    #"Changed Type"

Hope this helps.

[TheoC]

Hi @tiwari_456 

If I understand correctly, you can achieve this by going into Power Query and unpivoting the Date, Line 1, Line 2, and Line 3.

All you need to do in Power Query is select the columns (hold Ctrl button and click on the columns), then right click, and click on Unpivot.  It should work subject to your data structure being appropriate.

Let me know how this goes!

Theo