Normalize Data from SOL

[Anonymous]

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

[v-juanli-msft]

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]

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]

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]

Hi,

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

[Anonymous · Accepted solution]

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"