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
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?
Thanks so much for your help, JB. I was able to get this to work.
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