Forum Discussion

NPR's avatar
NPR
Frequent Visitor
7 years ago
Solved

How to input values into Table row constantly

I need to add values like this :   Col1  |  Col2  |  Col3 ---------------------- A          B            C D          E            F G         H            I     Please help me how to add val...
Need Help
Show and Tell
Tips and Tricks
  • v-diye-msft's avatar
    v-diye-msft
    7 years ago

    Hi NPR ,

     

    I created a sample as your required:

    Then add below M code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzmDSBUy6gkk3MOkOJj3ApKdSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.RoundUp( _ / 3), type number}}),
    #"Added Index1" = Table.AddIndexColumn(#"Divided Column", "Index.1", 1, 1),
    #"Divided Column1" = Table.TransformColumns(#"Added Index1", {{"Index.1", each Number.Mod (_ , 3), type number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Divided Column1", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Divided Column1", {{"Index", type text}}, "en-US")[Index]), "Index", "Column1"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index.1"})
in
    #"Removed Columns"

    get the result:

    Then you can rename the column.

     

PattemManohar's avatar
PattemManohar
Community Champion
7 years ago

NPR You mean, how to enter this data into table in Power BI ? If yes, then use "Enter Data" option

NPR's avatar
NPR
Frequent Visitor
7 years ago

No, i need to enter data into table horizortally. But (A,B,C,D.........) These items are in one column. Usually we can not add like this. That's why i am trying to add....! 

 

Col1 | Col2 | Col3

-------------------

A        B         C

D        E         F

G        H        I

 

Not like this 

 

Col1 | Col2 | Col3

-------------------

A

B

C

D

E

F

 

 

 

 

  • PattemManohar's avatar
    PattemManohar
    Community Champion
    7 years ago

    NPR You need to click on the asterisk symbol to create a new column

     

  • v-diye-msft's avatar
    v-diye-msft
    Community Support
    7 years ago

    Hi NPR ,

     

    I created a sample as your required:

    Then add below M code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzmDSBUy6gkk3MOkOJj3ApKdSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.RoundUp( _ / 3), type number}}),
    #"Added Index1" = Table.AddIndexColumn(#"Divided Column", "Index.1", 1, 1),
    #"Divided Column1" = Table.TransformColumns(#"Added Index1", {{"Index.1", each Number.Mod (_ , 3), type number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Divided Column1", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Divided Column1", {{"Index", type text}}, "en-US")[Index]), "Index", "Column1"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index.1"})
in
    #"Removed Columns"

    get the result:

    Then you can rename the column.