After appending, I get null values in primary table headers

Question

I have a table that I want to use as headers for another table that just has data. I used append as new in PBI, used the headers table as primary and data table as secondary. All the columns from the primary table have null values and the data table is appended next to headers column.

Eg:

Table 1 ( Headers)

+-----+-----+-----+-----+
| ABC | DEF | IGH | KLM |
+-----+-----+-----+-----+

Table 2 ( Data )

+----+----+----+----+
|  1 |  2 |  3 |  4 |
|  6 |  7 |  8 |  9 |
| 11 | 12 | 13 | 14 |
| 16 | 17 | 18 | 19 |
| 21 | 22 | 23 | 24 |
| 26 | 27 | 28 | 29 |
| 31 | 32 | 33 | 34 |
+----+----+----+----+

Table I am getting after append:


+------+------+------+------+------+------+------+------+
| ABC  | DEF  | IGH  | KLM  | null | null | null | null |
+------+------+------+------+------+------+------+------+
| null | null | null | null |    1 |    2 |    3 |    4 |
| null | null | null | null |    6 |    7 |    8 |    9 |
| null | null | null | null |   11 |   12 |   13 |   14 |
| null | null | null | null |   16 |   17 |   18 |   19 |
| null | null | null | null |   21 |   22 |   23 |   24 |
| null | null | null | null |   26 |   27 |   28 |   29 |
| null | null | null | null |   31 |   32 |   33 |   34 |
+------+------+------+------+------+------+------+------+

Table I need:


+-----+-----+-----+-----+
| ABC | DEF | IGH | KLM |
+-----+-----+-----+-----+
|   1 |   2 |   3 |   4 |
|   6 |   7 |   8 |   9 |
|  11 |  12 |  13 |  14 |
|  16 |  17 |  18 |  19 |
|  21 |  22 |  23 |  24 |
|  26 |  27 |  28 |  29 |
|  31 |  32 |  33 |  34 |
+-----+-----+-----+-----+

I used Append as new in PBI Used the headers table ( Table 1) as primary and appended Table 2 to it.

This shows at the top function:

= Table.Combine({Table 1, Table 2})

This in the advanced editor:

let
    Source = Table.Combine({Sheet1, InterviewQn})
in
    Source

Expected result:

+-----+-----+-----+-----+
| ABC | DEF | IGH | KLM |
+-----+-----+-----+-----+
|   1 |   2 |   3 |   4 |
|   6 |   7 |   8 |   9 |
|  11 |  12 |  13 |  14 |
|  16 |  17 |  18 |  19 |
|  21 |  22 |  23 |  24 |
|  26 |  27 |  28 |  29 |
|  31 |  32 |  33 |  34 |
+-----+-----+-----+-----+

OR

+-----+-----+-----+-----+
| ABC | DEF | IGH | KLM |
| 1   | 2   | 3   | 4   |
| 6   | 7   | 8   | 9   |
| 11  | 12  | 13  | 14  |
| 16  | 17  | 18  | 19  |
| 21  | 22  | 23  | 24  |
| 26  | 27  | 28  | 29  |
| 31  | 32  | 33  | 34  |
+-----+-----+-----+-----+

Olly · Accepted Answer · 2019-04-05T07:39:44.123

1

If you're only trying to rename the columns of Table 2, using the column names of Table 1, then it's simply:

= Table.RenameColumns(#"Table 2", List.Zip({Table.ColumnNames(#"Table 2"), Table.ColumnNames(#"Table 1")}))

See https://pwrbi.com/so_55529969/ for worked example PBIX file

edited Apr 05 '19 at 07:39

answered Apr 05 '19 at 07:32

Olly

7,749
1
19
38

I am new to Power BI, I didn't know I had to first apply the queries and then create a calculated table for a union. Spent too much of my time on this. Thank you though, appreciate it. https://learn.microsoft.com/en-us/power-bi/desktop-calculated-tables – Raul Singh Apr 05 '19 at 07:49
That would be another approach, using DAX. But the query line in my answer does what you want, in Power Query, before loading to data model. – Olly Apr 05 '19 at 08:06

After appending, I get null values in primary table headers

1 Answers1