Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
DataNat
Frequent Visitor

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?    

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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 

 

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

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

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
Not applicable

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

That would be so helpful, JB! Thank you.

Anonymous
Not applicable

Hi @DataNat 

 

This is the main table, after Change Types:

pbi1.png

 

This is the tHeaderConversion table:

pbi1.png

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:

pbi1.png

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

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?

One thing that's different between your scenario and mine is my datasource has already been replaced. I'm not trying to simply rename fields but replace them. My main table no longer has the old field names. Sale_Date has already been renamed Invoice Date, and now Sale_Date is no longer in the main table. All my calculations referencing Sale_Date are broken. The big question is how do I connect the Invoice Date to Sale_Date? Does that make sense?

 

Anonymous
Not applicable

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 

 

Thanks so much for your help, JB. I was able to get this to work. 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Top Solution Authors
Top Kudoed Authors