Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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.
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
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
That would be so helpful, JB! Thank you.
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
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?
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.
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
Thanks so much for your help, JB. I was able to get this to work.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.