Forum Discussion
Help Mapping Dynamic Number of Columns with Different Names Together
Hi Dion-NZ
No, it was based on the sample, I did not think about ID greater than 1 digit...so we need to adjust the steps split RECORD ID and digit a little bit
let Source = #"SYSTEMDataPostAudit (Source)", #"Filtered Rows" = Table.SelectRows(dbo_SYSTEMDataPostAudit, each ([IsRequest] = true)), #"Removed Irrelevant Columns" = Table.RemoveColumns(#"Filtered Rows",{"IsRequest", "SYSTEMDataPostActionId", "JobQueueItem", "SYSTEMDataPostAction"}), #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Split([DataPost],"&")), #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Custom", "Custom", Splitter.SplitTextByDelimiter("="), {"Custom", "Custom2"}), #"Added Custom1" = Table.AddColumn(#"Split Column by Delimiter", "index", each try Number.From(Text.Reverse( Text.BeforeDelimiter( Text.Reverse([Custom]),"_"))) otherwise 0), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom1", each if [index] = 0 then [Custom] else Text.Reverse( Text.AfterDelimiter( Text.Reverse([Custom]),"_"))), #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"DataPost", "Custom"}), JobTable = Table.SelectRows(#"Removed Columns", each ([index] = 0)), RecordTable = Table.SelectRows(#"Removed Columns", each ([index] <> 0)), PivotedTable = Table.Pivot(RecordTable, List.Distinct(RecordTable[Custom1]), "Custom1", "Custom2"), Custom1 = Table.Pivot(JobTable, List.Distinct(JobTable[Custom1]), "Custom1", "Custom2"), #"Removed Columns1" = Table.RemoveColumns(Custom1,{"index"}), #"Merged Queries" = Table.NestedJoin(#"Removed Columns1", {"LogID"}, PivotedTable, {"LogID"}, "Custom1", JoinKind.LeftOuter), #"Expanded Custom1" = Table.ExpandTableColumn(#"Merged Queries", "Custom1", List.RemoveItems( Table.ColumnNames( #"Merged Queries"[Custom1]{0}),{"LogID"})) in #"Expanded Custom1"You can try to work from here, and yes, it is VERY interesting journey, enjoy:)
Hi Dion-NZ
The second one is the output you want, or you mean you have more than one JobID in the datapost? Can you provide a sample?
Thanks, sorry for being unclear - I'll only one one job number in the datapost.
Here's a true datapost string - as you can see, they're a bit longer than my sample mockup... (and I made the job-level data bold, whereas the regular font is where the repetitive sequential record data starts - in this example, there were three records within one job).
ACTION=Add&CLIENT_NUMBER=TEST&AC=Y8&AD_ENTRY=EOL&FIRST_INSERTION=28%2f09%2f2016&COMPOSITE=N&COMPOSITE_CAPTION=&COMPOSITE_WIDTH=&COMPOSITE_DEPTH=&COMPOSITE_RATE=&COMPOSITE_RATE_CODE=&KEY_NUMBER=TEST&CREATIVE_NUMBER=&STATUS=P&PLAN_NUMBER_1=001223&RECORD_ID_1=&INSTRUCTION_NUMBER_1=&CAPTION_1=TESTCAPTION&ORDER_NUMBER_1=TESTORDER&PRODUCT_NUMBER_1=ZE&ADDITIONAL_DATA_A_1=&ADDITIONAL_DATA_B_1=&ADDITIONAL_DATA_C_1=&MEDIA_NUMBER_1=10015&POSITION_1=PND&INSERTION_DATE_1=28%2f09%2f2016&DATE_DESCRIPTION_1=&RATE_1=16.24&RATE_CODE_1=C&DEPTH_1=&WIDTH_1=2&SIZE_CODE_1=&COLOUR_CODE_1=&MEDIA_COM_1=20.00&REQD_COM_1=20.00&COMPANY_DISCOUNT_1=0.00&VACANCY_REF_1=&PLAN_NUMBER_2=001223&RECORD_ID_2=&INSTRUCTION_NUMBER_2=&CAPTION_2=TESTCAPTION&ORDER_NUMBER_2=TESTORDER&PRODUCT_NUMBER_2=ZE&ADDITIONAL_DATA_A_2=&ADDITIONAL_DATA_B_2=&ADDITIONAL_DATA_C_2=&MEDIA_NUMBER_2=10075&POSITION_2=PND&INSERTION_DATE_2=28%2f09%2f2016&DATE_DESCRIPTION_2=&RATE_2=4.76&RATE_CODE_2=C&DEPTH_2=&WIDTH_2=2&SIZE_CODE_2=&COLOUR_CODE_2=&MEDIA_COM_2=20.00&REQD_COM_2=20.00&COMPANY_DISCOUNT_2=0.00&VACANCY_REF_2=&PLAN_NUMBER_3=001223&RECORD_ID_3=&INSTRUCTION_NUMBER_3=&CAPTION_3=TESTCAPTION&ORDER_NUMBER_3=TESTORDER&PRODUCT_NUMBER_3=ZE&ADDITIONAL_DATA_A_3=&ADDITIONAL_DATA_B_3=&ADDITIONAL_DATA_C_3=&MEDIA_NUMBER_3=10134&POSITION_3=PND&INSERTION_DATE_3=28%2f09%2f2016&DATE_DESCRIPTION_3=&RATE_3=8.05&RATE_CODE_3=C&DEPTH_3=&WIDTH_3=2&SIZE_CODE_3=&COLOUR_CODE_3=&MEDIA_COM_3=20.00&REQD_COM_3=20.00&COMPANY_DISCOUNT_3=0.00&VACANCY_REF_3=
That's a lot🤣 you need to format all these? And from RECORD_ID, you need to "group" them within each ID?
Haha yes it is 😁 I want to sort of unpivot it so all the records (and their related data, such as COST, CAPTION, ORDER_NUMBER, etc) gets treated equally - so I'm imaging a separate related table of RECORD_ID, COST, ORDER NUMBER, etc... but that is tricky when even if I split using delimiters, the columns are technically named differently (_1, _2, etc... 😔
Hi Dion-NZ
Here is one attempt, you can work from here, the index should be the record counts
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZPPbtwgEMbfZaXeqhWe2Wy2Bw4EiIrqxS7GiZw04hL1Dfr+BYPZ9R92c7HM9zEznpmf3993jFvVaMo+P//8IwSOvFZSW6f785M01MrORp1xOpzSq3D+ihmobOqoPCvTWad0J82YDU7f4C/54R9AqmNK3JzbplNWUr0QHGftGLbUX5WwP1eqkO2GapjPvCU63ojJ+SWHdWfcSGbVi5ycqHaW2b6jbTy1NdPJdxUlpALA6BjJGyOcEl6Pih+DNf041ktIKhUb9edQPp2i5ZP4i/l+8Ecp1TeN8Ckv/pucdiFUSMJqJ5hljuVaS+ep6PDsnKVQ7FKk8o0+pA8IA42f3mqRG437Dmmkd7bWPlpCdtyoqfc0uBhTHfdwuFLCtrzMU3TYdQ4ZeQh10orUW74/bb5uejPXYk+eiRBI9oRMe/st1mpAh+nBCdXxpvf/gV92Nl8YZ5oPzsjnnP0aDCiAAWUwYAEG3AED7oABN8CAIhglh2dnBgYEMB6XYEARDPgaGHANBtDD/vG45ALmXMCMC1hzARtcwIoL2OQCbnIBBS5ggwsscIFlLnDBBd7hAu9wgTe4wCIXJYdnZ8YFei4qPCy4wCIX+DUu8JoLpKc9eVhygXMucMYFrrnADS5wxQVucoE3ucACF0h333fV7uPjPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DataPost = _t, LogID = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Split([DataPost],"&")), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Custom", "Custom", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Custom", "Custom2"}), #"Added Custom1" = Table.AddColumn(#"Split Column by Delimiter", "index", each try Number.From( Text.End([Custom],1)) otherwise 0), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom1", each if [index] = 0 then [Custom] else Text.Middle( [Custom],0,Text.Length([Custom])-2)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"DataPost", "Custom"}), JobTable = Table.SelectRows(#"Removed Columns", each ([index] = 0)), RecordTable = Table.SelectRows(#"Removed Columns", each ([index] <> 0)), PivotedTable = Table.Pivot(RecordTable, List.Distinct(RecordTable[Custom1]), "Custom1", "Custom2"), Custom1 = Table.Pivot(JobTable, List.Distinct(JobTable[Custom1]), "Custom1", "Custom2"), #"Removed Columns1" = Table.RemoveColumns(Custom1,{"index"}), #"Merged Queries" = Table.NestedJoin(#"Removed Columns1", {"LogID"}, PivotedTable, {"LogID"}, "Custom1", JoinKind.LeftOuter), #"Expanded Custom1" = Table.ExpandTableColumn(#"Merged Queries", "Custom1", List.RemoveItems( Table.ColumnNames( #"Merged Queries"[Custom1]{0}),{"LogID"})) in #"Expanded Custom1"