cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jwhitf4770
Frequent Visitor

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:

1_Quantity

1_Price

1_Total Cost

1_Description

2_Quantity

2_Price

2_Total Cost

2_Description

...

10_Quantity

10_Price

10_Total Cost

10_Description

 

So all of these return in 1 row.   I would like to make  a sub table of 10 rows with the following columns. 

Quantity

Price

Total Cost

Description

 

I apologize in advance I'm a .net programmer and SQL data person but this is my first Power BI experience.  

 

Thanks in Advance,

Justin

1 ACCEPTED SOLUTION

Accepted Solutions
jwhitf4770
Frequent Visitor

Re: Normalize Data from SOL

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"

View solution in original post

5 REPLIES 5
Community Support
Community Support

Re: Normalize Data from SOL

Hi @jwhitf4770

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

2.Added Custom

5.png

 

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

6.png

7.png

 

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"

 

 

8.png

 

Best Regards

Maggie

 

 

jwhitf4770
Frequent Visitor

Re: Normalize Data from SOL

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.

 

powerbi_normalized.JPG

jwhitf4770
Frequent Visitor

Re: Normalize Data from SOL

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:

powerbi_grouped.JPG 

 

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?

 

powerbi_grouped_table.JPG

 

 

Super User IV
Super User IV

Re: Normalize Data from SOL

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
jwhitf4770
Frequent Visitor

Re: Normalize Data from SOL

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"

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors