Forum Discussion

Dion-NZ's avatar
Dion-NZ
Frequent Visitor
5 years ago
Solved

Help Mapping Dynamic Number of Columns with Different Names Together

Hi there,   Hopefully someone can help me with this because I'm running out of hacky ideas to transform a table I'm working with. I'll do my best to explain...   I have a table that contains a lo...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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:)

Anonymous's avatar
Anonymous
Not applicable
5 years ago

That's a lot🤣 you need to format all these? And from RECORD_ID, you need to "group" them within each ID?

 

Dion-NZ's avatar
Dion-NZ
Frequent Visitor
5 years ago

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... 😔

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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"

     

    • Dion-NZ's avatar
      Dion-NZ
      Frequent Visitor
      5 years ago

      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"

       

       

      • Dion-NZ's avatar
        Dion-NZ
        Frequent Visitor
        5 years ago

        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...