Use first row as header.. but with a twist

Power Query
1

Hi,
I don’t let PowerBi do any data detection. I like to do it all myself so that’s it all clean and the steps are aligned.

However,
My current model appends a few files and i want to keep the first column (Source.Name). How do I use the first row as headers feature in this case? The issue here is that when i do a refresh and the new Source.Name file appears it’s going to be different and it will then give me an error of column header not found.

image
image891×282 18.7 KB

3

:thinking: Give this a go.

image
image908×147 8.44 KB

Table.Skip omits the first row (in yellow).
Within List.Zip pass the current column names as first list and the new column names as second list. You can extract the new column names from the first row Source{0} that returns a Record, use Record.ToList to transform that record into a list.

Here’s a sample. Just copy this code into a new blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs7PTfVLzE011EsuLlPSUfIsSc0FUiAhpVgdTHmX1OLskvwCIKs0s6gkL5GQqsqS1JzMcgKKCgorM8sylGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t, Column1 = _t, Column2 = _t]),
    PromoteHeaders = 
        Table.RenameColumns( 
            Table.Skip(Source, 1), 
            List.Zip( 
                { 
                    Table.ColumnNames(Source), 
                    Record.ToList( Source{0}) 
                } 
            )
        )
in
    PromoteHeaders

.
I hope this is helpful

5 Likes
4

Hi @chad.sharpe, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

5

Hi @chad.sharpe, we’ve noticed that no response has been received from you since the 11th of June. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

6

Hi @chad.sharpe, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks

7

It does the trick, I’ve been using it for a few days now. Sorry for the late reply, time gets away from me sometimes.

2 Likes