Forum Discussion
Need column in respective arrangement
- 2 years ago
As the number of tables are not clear, i recomend you follwo this step.
fits append the tables to reach the result like the next imageright click on column site and use unpivot other columns to reach the next result
then select Attribute column and go to transform tab and pick Pivot column command.
like the below image pic value for value column.hit ok to reach your result as below.
I hope this help you, if you have any other question, pls do not hesitate and ask
Here is an example of combining three tables with one similar column and then getting ride of null values.
Starting with...
and end up with...
Paste the code into the advanced editor of a blank query and you can review the steps...
let
tableOneSource = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjbUUQgyVNJRCgEynI3AnFgdqLgRsrgRQtwYWdxYKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [tableOneColumnOne = _t, tableOneColumnTwo = _t]),
tableTwoSource = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjbUUQgyVNJRCjHSUXA2AnNidaDiRsjiRghxY2RxY6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [tableTwoColumnOne = _t, tableTwoColumnTwo = _t]),
tableThreeSource = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjbUUQgyVNJRCjHWUXA2AnNidaDiRsjiRghxY2RxY6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [tableThreeColumnOne = _t, tableThreeColumnTwo = _t]),
combineNoRename =
Table.Combine(
{tableOneSource, tableTwoSource, tableThreeSource}
),
renameTableOneColumnOne =
Table.RenameColumns(tableOneSource, {"tableOneColumnOne", "ColumnOne"}),
renameTableTwoColumnOne =
Table.RenameColumns(tableTwoSource, {"tableTwoColumnOne", "ColumnOne"}),
renameTableThreeColumnOne =
Table.RenameColumns(tableThreeSource, {"tableThreeColumnOne", "ColumnOne"}),
combineColumnOneRename =
Table.Combine({renameTableOneColumnOne, renameTableTwoColumnOne, renameTableThreeColumnOne}),
groupByColumnOne =
Table.Group(
combineColumnOneRename,
{"ColumnOne"},
{
{"allRows", each _, type table [ColumnOne=nullable text, tableOneColumnTwo=nullable text, tableTwoColumnTwo=nullable text, tableThreeColumnTwo=nullable text]}
}
),
unpivotNonGroupedColumns =
Table.TransformColumns(
groupByColumnOne,
{
{"allRows", each Table.UnpivotOtherColumns(_, {"ColumnOne"}, "name", "value")}
}
),
transposeColumns =
Table.TransformColumns(
unpivotNonGroupedColumns,
{
{"allRows", each Table.PromoteHeaders(Table.Transpose(Table.SelectColumns(_, {"name", "value"})))}
}
),
expandInnerTables =
Table.ExpandTableColumn(
transposeColumns,
"allRows",
{"tableOneColumnTwo", "tableTwoColumnTwo", "tableThreeColumnTwo"}
)
in
expandInnerTables
Hope this gets you pointed in the right direction.
thanks for the reply and this provided example but there should be other way around as it is not applicable when we are dealing with higher number of columns for heavy data sets. I am looking forward for another solution.