Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Normalize Data from SOL

I am trying to create a Power BI report for an invoice.   It is a a list that was created in Sharepoint Online,  However when they created the list they did not nomalized the data.   I have columns...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    8 years ago

    I think I finally have the results I'm was expecting.  Does this languange have any methodololgy like .NET?    It is all copy a pasted sections with the different tables.   Just wondering if there is a way to make it more effecient.

     

    let

        Source = SharePoint.Tables("localhost", [ApiVersion = 14]),
        WarehouseInventoryAdjustmentRequest1 = Source{[Name="WarehouseInventoryAdjustmentRequest"]}[Content],
        #"Removed Columns" = Table.RemoveColumns(WarehouseInventoryAdjustmentRequest1,{"ContentTypeID", "X_ProjectOrWorkOrderNumber", "SDSValue", "X_ItemLongDescription", "MaterialOnHandValue", "X_PONumber", "X_ManufacturerPN", "RotatingItemValue", "Justification", "StatusValue", "RequestedById", "CommentsInstructionsForProcessing", "CreatedById", "ModifiedById", "Modified", "Created", "TotalCostAllItems", "SupervisorId", "DateRequested", "APP02_SupervisorDate", "APP03_ProcMgrId", "APP03_ProcMgrDate", "APP04_RefMgrId", "APP04_RefMgrDate", "DE01_ISIEById", "DE01_ISIEByDate", "DE02_IIEMById", "DE02_IIEMByDate", "WIA_00_GenerateRecordNumber", "ApprovalRejectionComments", "SupApproved", "ProcMgrApproved", "RefMgrApproved", "L01_20_SL", "L02_20_SL", "L03_20_SL", "L04_20_SL", "L05_20_SL", "S1", "S2", "WIA_01_InitialRequestEntryAndNotifications", "WIA_02_StatusChangeNotifications", "APP045_FinMgrDate", "Id", "ContentType", "Owshiddenversion", "Version", "Path", "SDS", "L01_13_SNS", "L01_07_UI", "L01_06_CS", "L01_05_ABC", "MaterialOnHand", "RotatingItem", "Status", "RequestedBy", "CreatedBy", "ModifiedBy", "L02_05_ABC", "L03_05_ABC", "L04_05_ABC", "L05_05_ABC", "L02_06_CS", "L03_06_CS", "L04_06_CS", "L05_06_CS", "L02_07_UI", "L03_07_UI", "L04_07_UI", "L05_07_UI", "L02_13_SNS", "L03_13_SNS", "L04_13_SNS", "L05_13_SNS", "Supervisor", "APP03_ProcMgr", "APP04_RefMgr", "DE01_ISIEBy", "DE02_IIEMBy", "L06_05_ABC", "L07_05_ABC", "L08_05_ABC", "L09_05_ABC", "L10_05_ABC", "L06_06_CS", "L07_06_CS", "L08_06_CS", "L09_06_CS", "L10_06_CS", "L06_07_UI", "L07_07_UI", "L08_07_UI", "L09_07_UI", "L10_07_UI", "L06_13_SNS", "L07_13_SNS", "L08_13_SNS", "L09_13_SNS", "L10_13_SNS", "L01_011_ACTION", "L02_011_ACTION", "L03_011_ACTION", "L04_011_ACTION", "L05_011_ACTION", "L06_011_ACTION", "L07_011_ACTION", "L08_011_ACTION", "L09_011_ACTION", "L10_011_ACTION", "L01_CS", "L02_CS", "L03_CS", "L04_CS", "L05_CS", "L06_CS", "L07_CS", "L08_CS", "L09_CS", "L10_CS", "Attachments"}),
        #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [RecordNumber] = RNParam),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {}, "Attribute", "Value"),
        #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each [Attribute]),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", Int64.Type}, {"Custom.3", type text}}),
        #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Custom.1", Order.Ascending}, {"Custom.2", Order.Ascending}}),

        #"null2" = Table.TransformColumns(#"Sorted Rows", {"Custom.3",  each if _ is null then "Null" else _}),
        LineItems = Table.Group(null2, {"Custom.3"}, {{"Count", each _, type table}}),

        
        IIN =LineItems{[Custom.3="IIN"]}[Count],
        #"Extracted Text Range" = Table.TransformColumns(IIN, {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
        #"Renamed Columns" = Table.RenameColumns(#"Extracted Text Range",{{"Attribute", "LineNumber"}, {"Value", "IIN"}}),
        #"IINFinal" = Table.RemoveColumns(#"Renamed Columns",{"Custom.1", "Custom.2", "Custom.3"}),

        ISD = LineItems{[Custom.3="ISD"]}[Count],
        #"EISD" = Table.TransformColumns(ISD, {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
        #"RISD" = Table.RenameColumns(#"EISD",{{"Attribute", "LineNumber"}, {"Value", "ISD"}}),
        #"ISDFinal" = Table.RemoveColumns(#"RISD",{"Custom.1", "Custom.2", "Custom.3"}),

        Merge1 = Table.NestedJoin(#"IINFinal",{"LineNumber"}, #"ISDFinal",{"LineNumber"},"ISD",JoinKind.FullOuter),
        #"ExpandedISD" = Table.ExpandTableColumn(Merge1, "ISD", {"ISD"}, {"ISD.ISD"}),

       
        VS = LineItems{[Custom.3="VS"]}[Count],
        #"EVS" = Table.TransformColumns(VS, {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
        #"RVS" = Table.RenameColumns(#"EVS",{{"Attribute", "LineNumber"}, {"Value", "VS"}}),
        #"VSFinal" = Table.RemoveColumns(#"RVS",{"Custom.1", "Custom.2", "Custom.3"}),
           
        #"MergeVS" = Table.NestedJoin(#"ExpandedISD",{"LineNumber"}, #"VSFinal",{"LineNumber"},"VS",JoinKind.FullOuter),
        #"ExpandedVS" = Table.ExpandTableColumn(#"MergeVS", "VS", {"VS"}, {"VS.VS"}),

        LT = LineItems{[Custom.3="LT"]}[Count],
        #"ELT" = Table.TransformColumns(LT, {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
        #"RLT" = Table.RenameColumns(#"ELT",{{"Attribute", "LineNumber"}, {"Value", "LT"}}),
        #"LTFinal" = Table.RemoveColumns(#"RLT",{"Custom.1", "Custom.2", "Custom.3"}),
           
        #"MergeLT" = Table.NestedJoin(#"ExpandedVS",{"LineNumber"}, #"LTFinal",{"LineNumber"},"LT",JoinKind.FullOuter),
        #"ExpandedLT" = Table.ExpandTableColumn(#"MergeLT", "LT", {"LT"}, {"LT.LT"}),

        ABCValue = LineItems{[Custom.3="ABCValue"]}[Count],
        #"EABCValue" = Table.TransformColumns(ABCValue, {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
        #"RABCValue" = Table.RenameColumns(#"EABCValue",{{"Attribute", "LineNumber"}, {"Value", "ABCValue"}}),
        #"ABCValueFinal" = Table.RemoveColumns(#"RABCValue",{"Custom.1", "Custom.2", "Custom.3"}),
           
        #"MergeABCValue" = Table.NestedJoin(#"ExpandedLT",{"LineNumber"}, #"ABCValueFinal",{"LineNumber"},"ABCValue",JoinKind.FullOuter),
        #"ExpandedABCValue" = Table.ExpandTableColumn(#"MergeABCValue", "ABCValue", {"ABCValue"}, {"ABCValue.ABCValue"}),

        #"CSValue" = LineItems{[Custom.3="CSValue"]}[Count],
        #"ECSValue" = Table.TransformColumns(#"CSValue", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
        #"RCSValue" = Table.RenameColumns(#"ECSValue",{{"Attribute", "LineNumber"}, {"Value", "CSValue"}}),
        #"CSValueFinal" = Table.RemoveColumns(#"RCSValue",{"Custom.1", "Custom.2", "Custom.3"}),
           
        #"MergeCSValue" = Table.NestedJoin( #"ExpandedABCValue",{"LineNumber"}, #"CSValueFinal",{"LineNumber"},"CSValue",JoinKind.FullOuter),
        #"ExpandedCSValue" = Table.ExpandTableColumn(#"MergeCSValue", "CSValue", {"CSValue"}, {"CSValue.CSValue"}),

        #"UIValue" = LineItems{[Custom.3="UIValue"]}[Count],
        #"EUIValue" = Table.TransformColumns(#"UIValue", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
        #"RUIValue" = Table.RenameColumns(#"EUIValue",{{"Attribute", "LineNumber"}, {"Value", "UIValue"}}),
        #"UIValueFinal" = Table.RemoveColumns(#"RUIValue",{"Custom.1", "Custom.2", "Custom.3"}),
           
        #"MergeUIValue" = Table.NestedJoin(#"ExpandedCSValue",{"LineNumber"}, #"UIValueFinal",{"LineNumber"},"UIValue",JoinKind.FullOuter),
        #"ExpandedUIValue" = Table.ExpandTableColumn(#"MergeUIValue", "UIValue", {"UIValue"}, {"UIValue.UIValue"}),


        #"CG" = LineItems{[Custom.3="CG"]}[Count],
        #"ECG" = Table.TransformColumns(#"CG", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
        #"RCG" = Table.RenameColumns(#"ECG",{{"Attribute", "LineNumber"}, {"Value", "CG"}}),
        #"CGFinal" = Table.RemoveColumns(#"RCG",{"Custom.1", "Custom.2", "Custom.3"}),
           
        #"MergeCG" = Table.NestedJoin(#"ExpandedUIValue",{"LineNumber"}, #"CGFinal",{"LineNumber"},"CG",JoinKind.FullOuter),
        #"ExpandedCG" = Table.ExpandTableColumn(#"MergeCG", "CG", {"CG"}, {"CG.CG"}),

        #"WL" = LineItems{[Custom.3="WL"]}[Count],
        #"EWL" = Table.TransformColumns(#"WL", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
        #"RWL" = Table.RenameColumns(#"EWL",{{"Attribute", "LineNumber"}, {"Value", "WL"}}),
        #"WLFinal" = Table.RemoveColumns(#"RWL",{"Custom.1", "Custom.2", "Custom.3"}),
           
        #"MergeWL" = Table.NestedJoin(#"ExpandedCG",{"LineNumber"}, #"WLFinal",{"LineNumber"},"WL",JoinKind.FullOuter),
        #"ExpandedWL" = Table.ExpandTableColumn(#"MergeWL", "WL", {"WL"}, {"WL.WL"}),


        #"BL" = LineItems{[Custom.3="BL"]}[Count],
        #"EBL" = Table.TransformColumns(#"BL", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
        #"RBL" = Table.RenameColumns(#"EBL",{{"Attribute", "LineNumber"}, {"Value", "BL"}}),
        #"BLFinal" = Table.RemoveColumns(#"RBL",{"Custom.1", "Custom.2", "Custom.3"}),
           
        #"MergeBL" = Table.NestedJoin(#"ExpandedWL",{"LineNumber"}, #"BLFinal",{"LineNumber"},"BL",JoinKind.FullOuter),
        #"ExpandedBL" = Table.ExpandTableColumn(#"MergeBL", "BL", {"BL"}, {"BL.BL"}),


        #"ACTIONValue" = LineItems{[Custom.3="ACTIONValue"]}[Count],
        #"EACTIONValue" = Table.TransformColumns(#"ACTIONValue", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
        #"RACTIONValue" = Table.RenameColumns(#"EACTIONValue",{{"Attribute", "LineNumber"}, {"Value", "ACTIONValue"}}),
        #"ACTIONValueFinal" = Table.RemoveColumns(#"RACTIONValue",{"Custom.1", "Custom.2", "Custom.3"}),
           
        #"MergeACTIONValue" = Table.NestedJoin(#"ExpandedBL",{"LineNumber"}, #"ACTIONValueFinal",{"LineNumber"},"ACTIONValue",JoinKind.LeftOuter),
        #"ExpandedACTIONValue" = Table.ExpandTableColumn(#"MergeACTIONValue", "ACTIONValue", {"ACTIONValue"}, {"ACTIONValue.ACTIONValue"}),

        #"Mvar" = LineItems{[Custom.3="M"]}[Count],
        #"EMvar" = Table.TransformColumns(#"Mvar", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
        #"RMvar" = Table.RenameColumns(#"EMvar",{{"Attribute", "LineNumber"}, {"Value", "Mvar"}}),
        #"MvarFinal" = Table.RemoveColumns(#"RMvar",{"Custom.1", "Custom.2", "Custom.3"}),
           
        #"MergeMvar" = Table.NestedJoin(#"ExpandedACTIONValue",{"LineNumber"}, #"MvarFinal",{"LineNumber"},"Mvar",JoinKind.FullOuter),
        #"ExpandedMvar" = Table.ExpandTableColumn(#"MergeMvar", "Mvar", {"Mvar"}, {"Mvar.Mvar"}),

        #"SNSValue" = LineItems{[Custom.3="SNSValue"]}[Count],
        #"ESNSValue" = Table.TransformColumns(#"SNSValue", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
        #"RSNSValue" = Table.RenameColumns(#"ESNSValue",{{"Attribute", "LineNumber"}, {"Value", "SNSValue"}}),
        #"SNSValueFinal" = Table.RemoveColumns(#"RSNSValue",{"Custom.1", "Custom.2", "Custom.3"}),
           
        #"MergeSNSValue" = Table.NestedJoin(#"ExpandedMvar",{"LineNumber"}, #"SNSValueFinal",{"LineNumber"},"SNSValue",JoinKind.FullOuter),
        #"ExpandedSNSValue" = Table.ExpandTableColumn(#"MergeSNSValue", "SNSValue", {"SNSValue"}, {"SNSValue.SNSValue"}),

        #"MQ" = LineItems{[Custom.3="MQ"]}[Count],
        #"EMQ" = Table.TransformColumns(#"MQ", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
        #"RMQ" = Table.RenameColumns(#"EMQ",{{"Attribute", "LineNumber"}, {"Value", "MQ"}}),
        #"MQFinal" = Table.RemoveColumns(#"RMQ",{"Custom.1", "Custom.2", "Custom.3"}),
           
        #"MergeMQ" = Table.NestedJoin(#"ExpandedSNSValue",{"LineNumber"}, #"MQFinal",{"LineNumber"},"MQ",JoinKind.FullOuter),
        #"ExpandedMQ" = Table.ExpandTableColumn(#"MergeMQ", "MQ", {"MQ"}, {"MQ.MQ"}),

        #"MXQ" = LineItems{[Custom.3="MXQ"]}[Count],
        #"EMXQ" = Table.TransformColumns(#"MXQ", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
        #"RMXQ" = Table.RenameColumns(#"EMXQ",{{"Attribute", "LineNumber"}, {"Value", "MXQ"}}),
        #"MXQFinal" = Table.RemoveColumns(#"RMXQ",{"Custom.1", "Custom.2", "Custom.3"}),
           
        #"MergeMXQ" = Table.NestedJoin(#"ExpandedMQ",{"LineNumber"}, #"MXQFinal",{"LineNumber"},"MXQ",JoinKind.FullOuter),
        #"ExpandedMXQ" = Table.ExpandTableColumn(#"MergeMXQ", "MXQ", {"MXQ"}, {"MXQ.MXQ"}),

        #"UC" = LineItems{[Custom.3="UC"]}[Count],
        #"EUC" = Table.TransformColumns(#"UC", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
        #"RUC" = Table.RenameColumns(#"EUC",{{"Attribute", "LineNumber"}, {"Value", "UC"}}),
        #"UCFinal" = Table.RemoveColumns(#"RUC",{"Custom.1", "Custom.2", "Custom.3"}),
           
        #"MergeUC" = Table.NestedJoin(#"ExpandedMXQ",{"LineNumber"}, #"UCFinal",{"LineNumber"},"UC",JoinKind.FullOuter),
        #"ExpandedUC" = Table.ExpandTableColumn(#"MergeUC", "UC", {"UC"}, {"UC.UC"}),

        #"TC" = LineItems{[Custom.3="TC"]}[Count],
        #"ETC" = Table.TransformColumns(#"TC", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
        #"RTC" = Table.RenameColumns(#"ETC",{{"Attribute", "LineNumber"}, {"Value", "TC"}}),
        #"TCFinal" = Table.RemoveColumns(#"RTC",{"Custom.1", "Custom.2", "Custom.3"}),
           
        #"MergeTC" = Table.NestedJoin(#"ExpandedUC",{"LineNumber"}, #"TCFinal",{"LineNumber"},"TC",JoinKind.LeftOuter),
        #"ExpandedTC" = Table.ExpandTableColumn(#"MergeTC", "TC", {"TC"}, {"TC.TC"}),

        #"FL" = LineItems{[Custom.3="FL"]}[Count],
        #"EFL" = Table.TransformColumns(#"FL", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
        #"RFL" = Table.RenameColumns(#"EFL",{{"Attribute", "LineNumber"}, {"Value", "FL"}}),
        #"FLFinal" = Table.RemoveColumns(#"RFL",{"Custom.1", "Custom.2", "Custom.3"}),
           
        #"MergeFL" = Table.NestedJoin(#"ExpandedTC",{"LineNumber"}, #"FLFinal",{"LineNumber"},"FL",JoinKind.FullOuter),
        #"ExpandedFL" = Table.ExpandTableColumn(#"MergeFL", "FL", {"FL"}, {"FL.FL"}),

        #"ILD" = LineItems{[Custom.3="ILD"]}[Count],
        #"EILD" = Table.TransformColumns(#"ILD", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
        #"RILD" = Table.RenameColumns(#"EILD",{{"Attribute", "LineNumber"}, {"Value", "ILD"}}),
        #"ILDFinal" = Table.RemoveColumns(#"RILD",{"Custom.1", "Custom.2", "Custom.3"}),
           
        #"MergeILD" = Table.NestedJoin(#"ExpandedFL",{"LineNumber"}, #"ILDFinal",{"LineNumber"},"ILD",JoinKind.FullOuter),
        #"ExpandedILD" = Table.ExpandTableColumn(#"MergeILD", "ILD", {"ILD"}, {"ILD.ILD"})


    in

           #"ExpandedILD"

v-juanli-msft's avatar
v-juanli-msft
Community Support
8 years ago

Hi Anonymous

1.Select all column, then click on “Unpivoted Columns”

2.Added Custom

 

3.Split Column by Delimiter, then remove the needless column

 

4.create a blank query

Code in advanced editor,

let

   source1=Table.SelectRows(Sheet2, each [Custom] = 1),

   source2=Table.SelectRows(Sheet2, each [Custom] = 2),

   source3=Table.SelectRows(Sheet2, each [Custom] = 3),

   source4=Table.SelectRows(Sheet2, each [Custom] = 4),

   #"Merged Queries1" = Table.NestedJoin(source1,{"Custom.1.1"},source2,{"Custom.1.1"},"source1",JoinKind.LeftOuter,

   #"Merged Queries2" = Table.NestedJoin(#"Merged Queries1",{"Custom.1.1"},source3,{"Custom.1.1"},"source3",JoinKind.LeftOuter),

   #"Merged Queries3" = Table.NestedJoin(#"Merged Queries2",{"Custom.1.1"},source4,{"Custom.1.1"},"source4",JoinKind.LeftOuter),

    #"Expanded source1" = Table.ExpandTableColumn(#"Merged Queries3", "source1", {"Attribute"}, {"source1.Attribute"}),

    #"Expanded source3" = Table.ExpandTableColumn(#"Expanded source1", "source3", {"Attribute"}, {"source3.Attribute"}),

    #"Expanded source4" = Table.ExpandTableColumn(#"Expanded source3", "source4", {"Attribute"}, {"source4.Attribute"})

in

   #"Expanded source4"

 

 

 

Best Regards

Maggie

 

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

This is really cool but I don't think the end result is what I was hoping.

 

I wanted a columns( Quanity,  Cost, Total Cost, Description) with the correpsonding values in rows.     Ithink the advanced query just needs to be a little different.  Thanks for the help and I look forward to your response.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    I seem to be closer but now I don't know how to combine the data (sorry) my data above was theoritcal where this is the real data)

     

    let

       source1=LineItemPivot,
       #"null2" = Table.TransformColumns(source1, {"Custom.3",  each if _ is null then "Null" else _}),
        #"Grouped Rows" = Table.Group(null2, {"Custom.3"}, {{"Count", each _, type table}})
    in
        #"Grouped Rows"

     

    Produces:

     

     

    Now if I click on the Table within here,  the data I want is in the order I want under value.   So how do I change "value" to the column name and then combine all these tables together?

     

     

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi,

       

      Share your PBI/Excel file and also show your expected result.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        I think I finally have the results I'm was expecting.  Does this languange have any methodololgy like .NET?    It is all copy a pasted sections with the different tables.   Just wondering if there is a way to make it more effecient.

         

        let

            Source = SharePoint.Tables("localhost", [ApiVersion = 14]),
            WarehouseInventoryAdjustmentRequest1 = Source{[Name="WarehouseInventoryAdjustmentRequest"]}[Content],
            #"Removed Columns" = Table.RemoveColumns(WarehouseInventoryAdjustmentRequest1,{"ContentTypeID", "X_ProjectOrWorkOrderNumber", "SDSValue", "X_ItemLongDescription", "MaterialOnHandValue", "X_PONumber", "X_ManufacturerPN", "RotatingItemValue", "Justification", "StatusValue", "RequestedById", "CommentsInstructionsForProcessing", "CreatedById", "ModifiedById", "Modified", "Created", "TotalCostAllItems", "SupervisorId", "DateRequested", "APP02_SupervisorDate", "APP03_ProcMgrId", "APP03_ProcMgrDate", "APP04_RefMgrId", "APP04_RefMgrDate", "DE01_ISIEById", "DE01_ISIEByDate", "DE02_IIEMById", "DE02_IIEMByDate", "WIA_00_GenerateRecordNumber", "ApprovalRejectionComments", "SupApproved", "ProcMgrApproved", "RefMgrApproved", "L01_20_SL", "L02_20_SL", "L03_20_SL", "L04_20_SL", "L05_20_SL", "S1", "S2", "WIA_01_InitialRequestEntryAndNotifications", "WIA_02_StatusChangeNotifications", "APP045_FinMgrDate", "Id", "ContentType", "Owshiddenversion", "Version", "Path", "SDS", "L01_13_SNS", "L01_07_UI", "L01_06_CS", "L01_05_ABC", "MaterialOnHand", "RotatingItem", "Status", "RequestedBy", "CreatedBy", "ModifiedBy", "L02_05_ABC", "L03_05_ABC", "L04_05_ABC", "L05_05_ABC", "L02_06_CS", "L03_06_CS", "L04_06_CS", "L05_06_CS", "L02_07_UI", "L03_07_UI", "L04_07_UI", "L05_07_UI", "L02_13_SNS", "L03_13_SNS", "L04_13_SNS", "L05_13_SNS", "Supervisor", "APP03_ProcMgr", "APP04_RefMgr", "DE01_ISIEBy", "DE02_IIEMBy", "L06_05_ABC", "L07_05_ABC", "L08_05_ABC", "L09_05_ABC", "L10_05_ABC", "L06_06_CS", "L07_06_CS", "L08_06_CS", "L09_06_CS", "L10_06_CS", "L06_07_UI", "L07_07_UI", "L08_07_UI", "L09_07_UI", "L10_07_UI", "L06_13_SNS", "L07_13_SNS", "L08_13_SNS", "L09_13_SNS", "L10_13_SNS", "L01_011_ACTION", "L02_011_ACTION", "L03_011_ACTION", "L04_011_ACTION", "L05_011_ACTION", "L06_011_ACTION", "L07_011_ACTION", "L08_011_ACTION", "L09_011_ACTION", "L10_011_ACTION", "L01_CS", "L02_CS", "L03_CS", "L04_CS", "L05_CS", "L06_CS", "L07_CS", "L08_CS", "L09_CS", "L10_CS", "Attachments"}),
            #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [RecordNumber] = RNParam),
            #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {}, "Attribute", "Value"),
            #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each [Attribute]),
            #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
            #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", Int64.Type}, {"Custom.3", type text}}),
            #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Custom.1", Order.Ascending}, {"Custom.2", Order.Ascending}}),

            #"null2" = Table.TransformColumns(#"Sorted Rows", {"Custom.3",  each if _ is null then "Null" else _}),
            LineItems = Table.Group(null2, {"Custom.3"}, {{"Count", each _, type table}}),

            
            IIN =LineItems{[Custom.3="IIN"]}[Count],
            #"Extracted Text Range" = Table.TransformColumns(IIN, {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
            #"Renamed Columns" = Table.RenameColumns(#"Extracted Text Range",{{"Attribute", "LineNumber"}, {"Value", "IIN"}}),
            #"IINFinal" = Table.RemoveColumns(#"Renamed Columns",{"Custom.1", "Custom.2", "Custom.3"}),

            ISD = LineItems{[Custom.3="ISD"]}[Count],
            #"EISD" = Table.TransformColumns(ISD, {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
            #"RISD" = Table.RenameColumns(#"EISD",{{"Attribute", "LineNumber"}, {"Value", "ISD"}}),
            #"ISDFinal" = Table.RemoveColumns(#"RISD",{"Custom.1", "Custom.2", "Custom.3"}),

            Merge1 = Table.NestedJoin(#"IINFinal",{"LineNumber"}, #"ISDFinal",{"LineNumber"},"ISD",JoinKind.FullOuter),
            #"ExpandedISD" = Table.ExpandTableColumn(Merge1, "ISD", {"ISD"}, {"ISD.ISD"}),

           
            VS = LineItems{[Custom.3="VS"]}[Count],
            #"EVS" = Table.TransformColumns(VS, {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
            #"RVS" = Table.RenameColumns(#"EVS",{{"Attribute", "LineNumber"}, {"Value", "VS"}}),
            #"VSFinal" = Table.RemoveColumns(#"RVS",{"Custom.1", "Custom.2", "Custom.3"}),
               
            #"MergeVS" = Table.NestedJoin(#"ExpandedISD",{"LineNumber"}, #"VSFinal",{"LineNumber"},"VS",JoinKind.FullOuter),
            #"ExpandedVS" = Table.ExpandTableColumn(#"MergeVS", "VS", {"VS"}, {"VS.VS"}),

            LT = LineItems{[Custom.3="LT"]}[Count],
            #"ELT" = Table.TransformColumns(LT, {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
            #"RLT" = Table.RenameColumns(#"ELT",{{"Attribute", "LineNumber"}, {"Value", "LT"}}),
            #"LTFinal" = Table.RemoveColumns(#"RLT",{"Custom.1", "Custom.2", "Custom.3"}),
               
            #"MergeLT" = Table.NestedJoin(#"ExpandedVS",{"LineNumber"}, #"LTFinal",{"LineNumber"},"LT",JoinKind.FullOuter),
            #"ExpandedLT" = Table.ExpandTableColumn(#"MergeLT", "LT", {"LT"}, {"LT.LT"}),

            ABCValue = LineItems{[Custom.3="ABCValue"]}[Count],
            #"EABCValue" = Table.TransformColumns(ABCValue, {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
            #"RABCValue" = Table.RenameColumns(#"EABCValue",{{"Attribute", "LineNumber"}, {"Value", "ABCValue"}}),
            #"ABCValueFinal" = Table.RemoveColumns(#"RABCValue",{"Custom.1", "Custom.2", "Custom.3"}),
               
            #"MergeABCValue" = Table.NestedJoin(#"ExpandedLT",{"LineNumber"}, #"ABCValueFinal",{"LineNumber"},"ABCValue",JoinKind.FullOuter),
            #"ExpandedABCValue" = Table.ExpandTableColumn(#"MergeABCValue", "ABCValue", {"ABCValue"}, {"ABCValue.ABCValue"}),

            #"CSValue" = LineItems{[Custom.3="CSValue"]}[Count],
            #"ECSValue" = Table.TransformColumns(#"CSValue", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
            #"RCSValue" = Table.RenameColumns(#"ECSValue",{{"Attribute", "LineNumber"}, {"Value", "CSValue"}}),
            #"CSValueFinal" = Table.RemoveColumns(#"RCSValue",{"Custom.1", "Custom.2", "Custom.3"}),
               
            #"MergeCSValue" = Table.NestedJoin( #"ExpandedABCValue",{"LineNumber"}, #"CSValueFinal",{"LineNumber"},"CSValue",JoinKind.FullOuter),
            #"ExpandedCSValue" = Table.ExpandTableColumn(#"MergeCSValue", "CSValue", {"CSValue"}, {"CSValue.CSValue"}),

            #"UIValue" = LineItems{[Custom.3="UIValue"]}[Count],
            #"EUIValue" = Table.TransformColumns(#"UIValue", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
            #"RUIValue" = Table.RenameColumns(#"EUIValue",{{"Attribute", "LineNumber"}, {"Value", "UIValue"}}),
            #"UIValueFinal" = Table.RemoveColumns(#"RUIValue",{"Custom.1", "Custom.2", "Custom.3"}),
               
            #"MergeUIValue" = Table.NestedJoin(#"ExpandedCSValue",{"LineNumber"}, #"UIValueFinal",{"LineNumber"},"UIValue",JoinKind.FullOuter),
            #"ExpandedUIValue" = Table.ExpandTableColumn(#"MergeUIValue", "UIValue", {"UIValue"}, {"UIValue.UIValue"}),


            #"CG" = LineItems{[Custom.3="CG"]}[Count],
            #"ECG" = Table.TransformColumns(#"CG", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
            #"RCG" = Table.RenameColumns(#"ECG",{{"Attribute", "LineNumber"}, {"Value", "CG"}}),
            #"CGFinal" = Table.RemoveColumns(#"RCG",{"Custom.1", "Custom.2", "Custom.3"}),
               
            #"MergeCG" = Table.NestedJoin(#"ExpandedUIValue",{"LineNumber"}, #"CGFinal",{"LineNumber"},"CG",JoinKind.FullOuter),
            #"ExpandedCG" = Table.ExpandTableColumn(#"MergeCG", "CG", {"CG"}, {"CG.CG"}),

            #"WL" = LineItems{[Custom.3="WL"]}[Count],
            #"EWL" = Table.TransformColumns(#"WL", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
            #"RWL" = Table.RenameColumns(#"EWL",{{"Attribute", "LineNumber"}, {"Value", "WL"}}),
            #"WLFinal" = Table.RemoveColumns(#"RWL",{"Custom.1", "Custom.2", "Custom.3"}),
               
            #"MergeWL" = Table.NestedJoin(#"ExpandedCG",{"LineNumber"}, #"WLFinal",{"LineNumber"},"WL",JoinKind.FullOuter),
            #"ExpandedWL" = Table.ExpandTableColumn(#"MergeWL", "WL", {"WL"}, {"WL.WL"}),


            #"BL" = LineItems{[Custom.3="BL"]}[Count],
            #"EBL" = Table.TransformColumns(#"BL", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
            #"RBL" = Table.RenameColumns(#"EBL",{{"Attribute", "LineNumber"}, {"Value", "BL"}}),
            #"BLFinal" = Table.RemoveColumns(#"RBL",{"Custom.1", "Custom.2", "Custom.3"}),
               
            #"MergeBL" = Table.NestedJoin(#"ExpandedWL",{"LineNumber"}, #"BLFinal",{"LineNumber"},"BL",JoinKind.FullOuter),
            #"ExpandedBL" = Table.ExpandTableColumn(#"MergeBL", "BL", {"BL"}, {"BL.BL"}),


            #"ACTIONValue" = LineItems{[Custom.3="ACTIONValue"]}[Count],
            #"EACTIONValue" = Table.TransformColumns(#"ACTIONValue", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
            #"RACTIONValue" = Table.RenameColumns(#"EACTIONValue",{{"Attribute", "LineNumber"}, {"Value", "ACTIONValue"}}),
            #"ACTIONValueFinal" = Table.RemoveColumns(#"RACTIONValue",{"Custom.1", "Custom.2", "Custom.3"}),
               
            #"MergeACTIONValue" = Table.NestedJoin(#"ExpandedBL",{"LineNumber"}, #"ACTIONValueFinal",{"LineNumber"},"ACTIONValue",JoinKind.LeftOuter),
            #"ExpandedACTIONValue" = Table.ExpandTableColumn(#"MergeACTIONValue", "ACTIONValue", {"ACTIONValue"}, {"ACTIONValue.ACTIONValue"}),

            #"Mvar" = LineItems{[Custom.3="M"]}[Count],
            #"EMvar" = Table.TransformColumns(#"Mvar", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
            #"RMvar" = Table.RenameColumns(#"EMvar",{{"Attribute", "LineNumber"}, {"Value", "Mvar"}}),
            #"MvarFinal" = Table.RemoveColumns(#"RMvar",{"Custom.1", "Custom.2", "Custom.3"}),
               
            #"MergeMvar" = Table.NestedJoin(#"ExpandedACTIONValue",{"LineNumber"}, #"MvarFinal",{"LineNumber"},"Mvar",JoinKind.FullOuter),
            #"ExpandedMvar" = Table.ExpandTableColumn(#"MergeMvar", "Mvar", {"Mvar"}, {"Mvar.Mvar"}),

            #"SNSValue" = LineItems{[Custom.3="SNSValue"]}[Count],
            #"ESNSValue" = Table.TransformColumns(#"SNSValue", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
            #"RSNSValue" = Table.RenameColumns(#"ESNSValue",{{"Attribute", "LineNumber"}, {"Value", "SNSValue"}}),
            #"SNSValueFinal" = Table.RemoveColumns(#"RSNSValue",{"Custom.1", "Custom.2", "Custom.3"}),
               
            #"MergeSNSValue" = Table.NestedJoin(#"ExpandedMvar",{"LineNumber"}, #"SNSValueFinal",{"LineNumber"},"SNSValue",JoinKind.FullOuter),
            #"ExpandedSNSValue" = Table.ExpandTableColumn(#"MergeSNSValue", "SNSValue", {"SNSValue"}, {"SNSValue.SNSValue"}),

            #"MQ" = LineItems{[Custom.3="MQ"]}[Count],
            #"EMQ" = Table.TransformColumns(#"MQ", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
            #"RMQ" = Table.RenameColumns(#"EMQ",{{"Attribute", "LineNumber"}, {"Value", "MQ"}}),
            #"MQFinal" = Table.RemoveColumns(#"RMQ",{"Custom.1", "Custom.2", "Custom.3"}),
               
            #"MergeMQ" = Table.NestedJoin(#"ExpandedSNSValue",{"LineNumber"}, #"MQFinal",{"LineNumber"},"MQ",JoinKind.FullOuter),
            #"ExpandedMQ" = Table.ExpandTableColumn(#"MergeMQ", "MQ", {"MQ"}, {"MQ.MQ"}),

            #"MXQ" = LineItems{[Custom.3="MXQ"]}[Count],
            #"EMXQ" = Table.TransformColumns(#"MXQ", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
            #"RMXQ" = Table.RenameColumns(#"EMXQ",{{"Attribute", "LineNumber"}, {"Value", "MXQ"}}),
            #"MXQFinal" = Table.RemoveColumns(#"RMXQ",{"Custom.1", "Custom.2", "Custom.3"}),
               
            #"MergeMXQ" = Table.NestedJoin(#"ExpandedMQ",{"LineNumber"}, #"MXQFinal",{"LineNumber"},"MXQ",JoinKind.FullOuter),
            #"ExpandedMXQ" = Table.ExpandTableColumn(#"MergeMXQ", "MXQ", {"MXQ"}, {"MXQ.MXQ"}),

            #"UC" = LineItems{[Custom.3="UC"]}[Count],
            #"EUC" = Table.TransformColumns(#"UC", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
            #"RUC" = Table.RenameColumns(#"EUC",{{"Attribute", "LineNumber"}, {"Value", "UC"}}),
            #"UCFinal" = Table.RemoveColumns(#"RUC",{"Custom.1", "Custom.2", "Custom.3"}),
               
            #"MergeUC" = Table.NestedJoin(#"ExpandedMXQ",{"LineNumber"}, #"UCFinal",{"LineNumber"},"UC",JoinKind.FullOuter),
            #"ExpandedUC" = Table.ExpandTableColumn(#"MergeUC", "UC", {"UC"}, {"UC.UC"}),

            #"TC" = LineItems{[Custom.3="TC"]}[Count],
            #"ETC" = Table.TransformColumns(#"TC", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
            #"RTC" = Table.RenameColumns(#"ETC",{{"Attribute", "LineNumber"}, {"Value", "TC"}}),
            #"TCFinal" = Table.RemoveColumns(#"RTC",{"Custom.1", "Custom.2", "Custom.3"}),
               
            #"MergeTC" = Table.NestedJoin(#"ExpandedUC",{"LineNumber"}, #"TCFinal",{"LineNumber"},"TC",JoinKind.LeftOuter),
            #"ExpandedTC" = Table.ExpandTableColumn(#"MergeTC", "TC", {"TC"}, {"TC.TC"}),

            #"FL" = LineItems{[Custom.3="FL"]}[Count],
            #"EFL" = Table.TransformColumns(#"FL", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
            #"RFL" = Table.RenameColumns(#"EFL",{{"Attribute", "LineNumber"}, {"Value", "FL"}}),
            #"FLFinal" = Table.RemoveColumns(#"RFL",{"Custom.1", "Custom.2", "Custom.3"}),
               
            #"MergeFL" = Table.NestedJoin(#"ExpandedTC",{"LineNumber"}, #"FLFinal",{"LineNumber"},"FL",JoinKind.FullOuter),
            #"ExpandedFL" = Table.ExpandTableColumn(#"MergeFL", "FL", {"FL"}, {"FL.FL"}),

            #"ILD" = LineItems{[Custom.3="ILD"]}[Count],
            #"EILD" = Table.TransformColumns(#"ILD", {{"Attribute", each Text.Middle(_, 1, 2), type text}}),
            #"RILD" = Table.RenameColumns(#"EILD",{{"Attribute", "LineNumber"}, {"Value", "ILD"}}),
            #"ILDFinal" = Table.RemoveColumns(#"RILD",{"Custom.1", "Custom.2", "Custom.3"}),
               
            #"MergeILD" = Table.NestedJoin(#"ExpandedFL",{"LineNumber"}, #"ILDFinal",{"LineNumber"},"ILD",JoinKind.FullOuter),
            #"ExpandedILD" = Table.ExpandTableColumn(#"MergeILD", "ILD", {"ILD"}, {"ILD.ILD"})


        in

               #"ExpandedILD"