Forum Discussion

tiwari_456's avatar
tiwari_456
Regular Visitor
2 years ago

Convert from Column to Row.

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 + c...
Need Help
Show and Tell
Ashish_Mathur's avatar
Ashish_Mathur
Super User
2 years ago

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.