0

I need to do exactly what is described here: Replace all error values of all columns after importing datas (while keeping the rows).

What I don't know is how to integrate this code into the existing code within Power Query. Currently I have the code below, producing a sample file which is then applied to a number of files originating from a folder. I need to get the code after the row starting #"Promoted Headers" and before the "in" row, as already indicated, not sure how? I would be grateful for some expert advice!

let
Source = Excel.Workbook(#"Sample File Parameter1", null, true),
#"Filtered Rows" = Table.SelectRows(Source, each ([Hidden] = false)),
#"Master doc_Sheet" = #"Filtered Rows"{[Item="Master doc",Kind="Sheet"]}[Data],
#"Removed Top Rows" = Table.Skip(#"Master doc_Sheet",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true])
in #"Promoted Headers"
Zack E
  • 696
  • 7
  • 23
Matt C
  • 1

1 Answers1

0

So you want to replace all errors, after promoting headers:

let
    Source = Excel.Workbook(#"Sample File Parameter1", null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Hidden] = false)),
    #"Master doc_Sheet" = #"Filtered Rows"{[Item="Master doc",Kind="Sheet"]}[Data],
    #"Removed Top Rows" = Table.Skip(#"Master doc_Sheet",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Promoted Headers", List.Transform(Table.ColumnNames(#"Promoted Headers"), each {_, null}))
in
    #"Replaced Errors"
Olly
  • 7,749
  • 1
  • 19
  • 38
  • Thank you for your reply. Forgive me, I am a complete amateur beyond the button clicking of the Power Query user interface. The code in the original thread seemed more extensive; specifically to deal with a situation where the source files may have their headings changed (the situation I have). Does this solution deal with this scenario? Thanks again. – Matt C Feb 14 '19 at 09:31