Forum Discussion

DataNat's avatar
DataNat
Frequent Visitor
6 years ago
Solved

Report broke after replacing data source with renamed fields

I am connected to the sql server data source via import mode.  My sql statement references a view. I recently replaced this view with a new one, containing a combination of new fields and about 80 renamed existing fields (i.e. 'Sales_Date' and now 'Invoice Date'). The problem I am running into involves these renamed fields. Because they were renamed, the orignal fields seemed to have disappeared, leaving broken visuals throughout my Power BI desktop report. I have read numerous PBI community posts suggesting that I make edits in the Advanced Editor. I did that do not see the original field names anywhere. When replacing a dataset in my scenario, how can I tell PBI to also replace existing fields with new ones?    

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Natalie,

     

    In my example Old and New columns have a bit differrent semantics, this is my fault, sorry, it makes it confusing in your case.

     

    You did everything right. There is just a couple of things that need to be changed and hopefully everything will work.

    1. In your tHeaderConversion table there should not be any nulls, becasuse it represents column names and column names can not be empty. If the column names does not need to be changed, not include it in the table (I guess, this is the case for something like DC2 .
    2. Correct, in your version it should be #"Change type", not #"Changed column type"
    3. As you rightfully noticed you need to rename columns from "new" to "old" to make the existing part of the code work. For this you need to swap the arguments inside the List.Zip({}).

     

    List.Zip({tHeaderConversion[New columns], tHeaderConversion[Old columns]})

     

     

    This is the updated code that will hopefully do what we want:

     

    let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hVbBbuMgEP0VlHN72T9InLaKdptEcbtSVVWI2NRF64CFcdX8/c4wYJvEbi8x780bZoAZyOvrwqpycbNYvN28LnLT2ULy/Nw6eQKSMAsYFYKvrNDoQF/kNvqT54avhZPAAzIKnDxE85O0Vjljz3wt2wIUPcE84QOLWraNtK3RfCtOOI+n2EE2XpB1rePbowUDDtm2Ox2lTUzRkew4RuteWKldpAPqQ/LsQ2gta7SHEdp2tpSWP50bnM8D5gHaloVTn5Lf16ICIyHmkZ/1QzU8U+6MK4DxrTO3HqIRluuUFk7BKjPTaWcTWWC8MvsVz+QgK9ADCgMfpTvyngfARrbNOnoWsEJdCsvPEn5ORrsPXGdg2Quw7NGzqEbId+98A0kYf47w9SIK+fuZw6ZjvH/dePs3UBph53E4nOndl4NAsvTHblXjKN1ID8JYUXFpEfdD4qE8MqipytCuJdjXwWBM+L01ZVc4nkurZAvWQLBAjDSDsV8zxGlqJbHwALIe0tEUxpaxTggNhQJJiNpUcNKlDDkBZB5S3VooxuL8GA8m4NGZRGpv5eeVDMmRNvQdn2il2JJpS626s9IVf7Cma7CfPWQExxNS18U5LhrPnKBR9hZ1cSJqNKRGk2XGNsbCFow7LnJs3Hvb5V/ez7zFSgSGXTY9quA8ghVHdCDN1fUxXAW+ZMZ9A3DcOcvDc772fmCFIYPm7kTNDvEmyy4FmdGtE9pNSfj+ZUrFgO6DZbuHHDcCv2w24pVqJizaKeqMNImduzIzLZZ87oRj36cwJ57bAJJTMt94DAlBeT0KWylNpcYITCQzI5xKpJdSHnPqmMT9fjncEQDG90PjK/xI9xLV9ireTCvRQlWHrozdjiTrW7Xv+buvAkq9kvxAj2XEQ9rpbLHYqJqTSafUE6f0k09SXD+JZzb/2g2uJqk7yUVR4KvGtfFvpQeJwGh4E60Up1R6gKttF00sOI77v8DySlxw5bPKmTDe59s4taxEzeE/A7zhXNPV8gc5due54WgTZZk8eYkDPHvRQn8nKJ+sFm2r3lUhglNM5sLw9vYf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Old Column" = _t, #"New Column" = _t]),
  #"Changed Column Type" = Table.TransformColumnTypes(Source, {{"Old Column", type text}, {"New Column", type text}}),
  #"Renamed Columns" = Table.RenameColumns(#"Changed Column Type", {{"Old Column", "Old columns"}, {"New Column", "New columns"}}),
  #"Filtered rows" = Table.SelectRows(#"Renamed Columns", each ([New columns] <> ""))
in
  #"Filtered rows"

     

     

     

    let
    Source = Sql.Database("SQLNUM", "DBName", [Query="SELECT * FROM view_name),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column not Related to this Isse", Int64.Type}}), //This code is from a non-related step
  #"Rename Columns" = Table.RenameColumns(#"Changed Type", List.Zip({tHeaderConversion[New columns], tHeaderConversion[Old columns]}))
in
  #"Rename Columns"

     

     

    To answer your last question: once the columns are renamed to "old" the existing code should work again.

     

    Kind regards,

    JB

    DataNat 

     

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Hi DataNat

    If you have a table that matches old and new column names it is reasonably easy to rename new field names to old at the beginning of the query and then back to new names at the end.

    Something like
    Table.RenameColumns(Source, List.Zip({Source[OldNames],Source[NewNames]}))

    Kind regards
    JB
    • DataNat's avatar
      DataNat
      Frequent Visitor
      6 years ago

      Hi JB,

       

      I tried the first step to rename new field names to old for just the old (Sales_Date) and new sales (Invoice Date) fields but received an error that the old sales column wasn't found.  Like I mentioned when I replaced my data source, original fields that were renamed are gone. Am I doing something in the wrong order? 

       

      Here is the code I used which went under Source and Changed Type. Changed Type contains only one type changed for a new field, not related to this issue: 

       

      Table.RenameColumns(Source, List.Zip({Source[Invoice Date],Source[Sales_Date]}))

       

      Thanks,

      Natalie

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago
        Hi

        Sorry, I was unclear. The rename columns works slightly different - column name.go in as text.

        Table.RenameColumns(Source, {"OldColumnName","NewColumnName"})

        But if you need to rename 80 columns, it becomes quite painful.

        Possible solution is to create a separate table that look like
        Old columns New columns
        Sales_Date Invoice Date
        OldColumn1 NewColumn1
        Old Column2 NewColumn2
        ...

        You can create it via Enter Data in PQ editor.
        Let's assume this table above is called tHeaderConversions. In this case the step that you need to add to your main query will be:

        Table.RenameColumns(Source, List.Zip({tHeaderConversion[Old columns],tHeaderConversion[New columns]})
        //p.s. by the way in your case you probably need to put #"Changed Type" or whatever this step is called instead of Source
        I do not have access to PBI at the moment, so I can't test and send you the working snippet. I will do a better example later today if I can.

        Kind regards
        JB