Forum Discussion
Report broke after replacing data source with renamed fields
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.
- 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 .
- Correct, in your version it should be #"Change type", not #"Changed column type"
- 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
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.
- 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 .
- Correct, in your version it should be #"Change type", not #"Changed column type"
- 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
Thanks so much for your help, JB. I was able to get this to work.