Forum Discussion

philipsr1's avatar
philipsr1
Regular Visitor
6 years ago
Solved

Need help on adding new rows from colums

Need some advice .I have a table with a row contraining three receive dates and three receive amounts. I am looking for a way to convert them into three rows  as shown below

 

Original

RECACCTRECDATE1RECAMT1RECDATE2RECAMT1RECDATE3RECAMT1
300042019111333009.0520200417-41860.252020041741860.25
300052020041723993.71000000000000000000

 

Desired output

 

RECACCT  RECDATE              RECAMT

30004        20191113               33009.05

30004        20200417             -41860.25

30004        20200417              41860.25

30005        20200417              23993.71

 

Any help would be appriciated

 

Thanks

  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    This M code works

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RECACCT", type text}, {"RECDATE1", Int64.Type}, {"RECAMT1", type number}, {"RECDATE2", Int64.Type}, {"RECAMT12", type number}, {"RECDATE3", Int64.Type}, {"RECAMT13", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"RECACCT"}, "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"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition",{"Attribute.2"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns", {{"RECACCT", type text}}, "en-IN"),{"RECACCT", "Attribute.1"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Added Index" = Table.AddIndexColumn(#"Merged Columns", "Index", 1, 1),
    Partition = Table.Group(#"Added Index", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Value", "Index", "Index1"}, {"Value", "Index", "Index1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Partition",{{"Index", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns1", "Merged", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"Merged.1", "Merged.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Merged.2]), "Merged.2", "Value"),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Index1"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns2",{{"RECAMT", type number}})
in
    #"Changed Type2"

    Hope this helps.

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    This M code works

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RECACCT", type text}, {"RECDATE1", Int64.Type}, {"RECAMT1", type number}, {"RECDATE2", Int64.Type}, {"RECAMT12", type number}, {"RECDATE3", Int64.Type}, {"RECAMT13", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"RECACCT"}, "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"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition",{"Attribute.2"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns", {{"RECACCT", type text}}, "en-IN"),{"RECACCT", "Attribute.1"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Added Index" = Table.AddIndexColumn(#"Merged Columns", "Index", 1, 1),
    Partition = Table.Group(#"Added Index", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Value", "Index", "Index1"}, {"Value", "Index", "Index1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Partition",{{"Index", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns1", "Merged", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"Merged.1", "Merged.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Merged.2]), "Merged.2", "Value"),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Index1"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns2",{{"RECAMT", type number}})
in
    #"Changed Type2"

    Hope this helps.

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    philipsr1 you need to unpivot your table

     

    - click transform data

    - select RECACCT column 

    - right-click and select unpivot other columns

     

    you will get two new columns, attribute, and value

     

    - rename attribute to RecDate and value to Amount

     

    and now you can visualize the data the way you want. and it is best practice from the modeling perspective.

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    • philipsr1's avatar
      philipsr1
      Regular Visitor
      6 years ago

      Unfortunately unpivot does not work in my scenario. Unpivot as I understand works for example , when you have data of sales for mutiple Years. So the heading like 2015,2016,2017, 2018 as column headings with sale amounts of each of them under it. So when you unpivot it converts years(the heading) as attributes and sales as values. My case is different. I have
      RECDATE1,RECAMT1,RECDATE2,RECAMT2,RECDATE3,RECAMT3 as headings and corresponding values underneath it. and I am looking for a way to create two colums with the value of RECDATE in one column and the corresponding RECAMT value on the other.I made a typo on my heading which might have caused the confusion.

      I am entering the input and the desired output

      Original
      RECACCT           RECDATE1            RECAMT1            RECDATE2     RECAMT2         RECDATE3     RECAMT3
      30004                20191113             33009.05             20200417     -41860.25        20200417      41860.25
      30005                20200417             23993.71             00000000      0                       00000000      0

       

      Desired output

       

      RECACCT      RECDATE      RECAMT

      30004           20191113     33009.05

      30004           20200417    -41860.25

      30004           20200417     41860.25

      30005           20200417     23993.71

       

      Thanks