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 re...
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 

     

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
  • DataNat's avatar
    DataNat
    Frequent Visitor
    6 years ago

    That would be so helpful, JB! Thank you.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi DataNat 

       

      This is the main table, after Change Types:

       

      This is the tHeaderConversion table:

      This is the code to generate it - in your version the simpliest way to go - create a table in Excel and then "Enter Data" in Power BI and copy-paste the table from Excel. The code below just for testing how it works.

      let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MSY13SSxJVdJR8swry89MTlUAc2N1opX8c1LinfNzSnPz4g2B8n6p5QgumrwRqryRUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Old columns" = _t, #"New columns" = _t])
in
  Source

       

      This is the main table code (the first image above relates to the step called #"Changed column type"

      let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyUNJRckksSTSE0QiGoVKsTrSSEbIqIxiNYBgpxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Sale_Date = _t, Old_Column_1 = _t, Old_Column_2 = _t, Old_Column_3 = _t]),
  #"Changed column type" = Table.TransformColumnTypes(Source, {{"Sale_Date", type date}, {"Old_Column_1", type text}, {"Old_Column_2", type text}, {"Old_Column_3", type text}}),
  
  #"Rename Columns" = Table.RenameColumns(#"Changed column type", List.Zip({tHeaderConversion[Old columns], tHeaderConversion[New columns]}))
in
  #"Rename Columns"

       This is the output of the query:

      Notice how it only affects the columns that are in the conversion table (i.e. need to be renamed).

      In your original query, please make sure that nothing refers to steps earlier than "Rename Columns". In the desktop if you click on the step name in the Advanced Editor it will highlight all references to the step - quite handy ğŸ™‚.

       

      After you process the query, you can reverse the header conversion by using the same code, but swaping the arguments of List.Zip(). I may be a bit trickier, if you remaning or deleting some columns while processing query.

       

      Kind regards,

      JB

      • DataNat's avatar
        DataNat
        Frequent Visitor
        6 years ago

        I tried to follow your code as closely as I can. 

         

        Here is my  tHeaderConversion code:

        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"}})
in
    #"Renamed Columns"

         

        Here is my main table code:

        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 column type", List.Zip({tHeaderConversion[Old columns], tHeaderConversion[New columns]}))
in
  #"Rename Columns"

         

        I get the following error with the main table code: Expression.Error: The name 'Changed column type' wasn't recognized. Make sure it's spelled correctly.

         

        I tried changing 'Changed column type' to 'Changed Type' from the line above and got this error:

        Expression.Error: The field '' already exists in the record.
        Details:
        Name=
        Value=

         

        I don't see how the two lines are related or why they need to be. since the Changed Type code is for an unrelated field. Any ideas what I did wrong?