Urgently needs help in "Transposing Columns to rows"

[navedkhan]

Hi Guys - i am in URGENT need to do the following;

Requirement: move columns (measure-2 & comments) as shown above to append beneath a given column [measure-1 & comments] and fill dates for the said no. of entries being added (reason being both columns contain same information) and hence need to be clubbed in one.

Any help anyone please?

[mussaenda]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLQN7DUNzIwtFTSUXLMKchIBNLJKcUpSNzU8tTyIqVYHTTVTqklEMVp6UDKPTE3F8RNLE5MSUvFVA2TTytOASpBaC9KTS8qSwbah6EDgqIgyrLSM9KUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t, #"measure-1" = _t, #"comments-1" = _t, #"measure-2" = _t, #"comments-2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"measure-1", type text}, {"comments-1", type text}, {"measure-2", type text}, {"comments-2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"measure-1", "comments-1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"measure-2", "measure"}, {"comments-2", "comments"}})
in
    #"Renamed Columns"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLQN7DUNzIwtFTSUXLMKchIBNLJKcUpSNzU8tTyIqVYHTTVTqklEMVp6UDKPTE3F8RNLE5MSUvFVA2TTytOASpBaC9KTS8qSwbah6EDgqIgyrLSM9KUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t, #"measure-1" = _t, #"comments-1" = _t, #"measure-2" = _t, #"comments-2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"measure-1", type text}, {"comments-1", type text}, {"measure-2", type text}, {"comments-2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"measure-2", "comments-2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"measure-1", "measure"}, {"comments-1", "comments"}}),
    #"Appended Query" = Table.Combine({#"Renamed Columns", #"Table (3)"})
in
    #"Appended Query"

[Anonymous · Accepted solution]

Hello-

  One option is to duplicate your table.  In one table, remove all but date, measure-1, and comments-1 (renamed to comments-1).

In the other remove all but date, measure-2 and comments-2(renamed comments-2).

After this, rename: measure-1 to measure and measure-2 to measure.  Rename comments-1 to comments and comments-2 to comments.

Select Append queries.  They should append as 3 columns: date, measure, and comments.

Jared