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:)
Thanks again for all your help on this! I'm almost there using your Power Query (and my Power Query knowledge is extremely basic, so it's been an itneresting journey walking through it step by step!), but I'm getting an error...
Expression.Error: The 'offset' argument is out of range.
Details:
0
And at one point it had a Go to error link that seems to point at the PivotedTable step as the culprit. I've tried reading up on it, but I can't find an 'offset' argument in these statements..?
I'll post my Power Query below - please note that my original source is actually from an Azure SQL Server database, so for now I'm referencing that original untouched query as "SYSTEMDataPostAudit (Source)".
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],"&")),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"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.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"
Oh, and one more question - the statement being used for truncating the record sequence off the end (RECORD_ID_1 becomes RECORD_ID, COST_1 becomes COST, etc) ... will that also cater for when that sequence has more than one digit? I had a quick look in the database, and found that we definitely have job's with 10+ associated records, which would mean RECORD_ID_23, COST_23, etc...
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:)