Get values and put evenly from merged cells

[Anonymous]

Hi all,

Can we transform this type of unstructured template to structured to be read using query editor or dax?

like 2023 Q1 QTY/ 2023 Q1 OI/ 2023 Q2 QTY/ 2023 Q2 OI/ .... / 2023 Total QTY / 2023 Total OI

Not all the merged cells are recognized with values so this is the issue.

and year number 2023 will be 2024, 2025, 2026,,,, so I cannot forcelly rename them with particular year number in query editor. 

*year number will be updated on data source sheet name. 

To summarize, 

2023 is year number so it is dynamic

QTY/OI is fixed column which always be there

[Anonymous]

Hi @Anonymous ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data on excel.

(2) Click "transform data" to enter the power query, click "Advanced Editor", copy and paste the following code, please pay attention to replace it with your data source.

let
    Source = Excel.Workbook(File.Contents("C:\Users\TEST.xlsx"), null, true),
    Tabelle1_Sheet = Source{[Item="Tabelle1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Tabelle1_Sheet,{{"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1", "Column2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Filled Down",{{"Column4", Int64.Type}}),
    #"Transposed Table1" = Table.Transpose(#"Changed Type2"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"2023", type any}, {"2023_1", type text}, {"2023_2", type text}, {"2023_3", type text}, {"2024", type any}, {"2024_4", type any}, {"2024_5", type any}, {"2024_6", type any}})
in
    #"Changed Type1"

(3) Then the result is as follows.

When the data in your excel is updated, click refresh and the data in power query will be updated automatically.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Anonymous]

Hi,

Thanks for your help but the result you showed doesn't seem to combine all attributes such year-quarter-Qty or OI in one cell. 

The desired column name would be like,

2023 Q1 QTY/ 2023 Q1 OI/ 2023 Q2 QTY/ 2023 Q2 OI/ .... / 2023 Total QTY / 2023 Total OI.

then I can sort them out in data table view adding new columns.

FYI,

here's the current query:

let
Source = SharePoint.Contents("https://XXX.sharepoint.com/sites/korea-pmo-pbi-data-repository", [ApiVersion = 15]),
#"Shared Documents" = Source{[Name="Shared Documents"]}[Content],
#"KR PO Plan" = #"Shared Documents"{[Name="KR PO Plan"]}[Content],
SKT = #"KR PO Plan"{[Name="SKT"]}[Content],
#"SK_5G Bottom up plan" = SKT{[Name="CT SK_5G Bottom up plan"]}[Content],
#"Filtered Hidden Files1" = Table.SelectRows(#"SK_5G Bottom up plan", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Sorted Rows" = Table.Sort(#"Expanded Table Column1",{{"Source.Name", Order.Ascending}}),
#"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Source.Name", type text}, {"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type any}, {"Column14", type text}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type text}, {"Column28", type text}, {"Column29", type any}, {"Column30", type any}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> null and [Column1] <> "Project")),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Column12", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30"}),
#"Renamed Columns3" = Table.RenameColumns(#"Removed Columns1",{{"Column1", "Project"}, {"Column2", "Product"}, {"Column3", "Product Detail"}, {"Column4", "Category"}, {"Column5", "Item"}, {"Column6", "LE"}, {"Column7", "Material Code (BoQ level)"}, {"Column8", "Trim Material"}, {"Column9", "GIC"}, {"Column10", "Item Detail"}, {"Column11", "Unit Price(KRW)"}, {"Column13", "2023_1Q_QTY"}, {"Column14", "2023_1Q_OI"}, {"Column15", "2023_2Q_QTY"}, {"Column16", "2023_2Q_OI"}, {"Column17", "2023_3Q_QTY"}, {"Column18", "2023_3Q_OI"}, {"Column19", "2023_4Q_QTY"}, {"Column20", "2023_4Q_OI"}, {"Column21", "2023_FY_QTY"}, {"Column22", "2023_FY_OI"}, {"Column23", "Chk"}}),
#"Added Index" = Table.AddIndexColumn(#"Renamed Columns3", "#", 1, 1, Int64.Type),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Source.Name", "Project", "Product", "Product Detail", "Category", "Item", "LE", "Material Code (BoQ level)", "Trim Material", "GIC", "Item Detail", "Unit Price(KRW)", "Chk", "#"}, "Attribute", "Value"),
#"Added Index1" = Table.AddIndexColumn(#"Unpivoted Columns", "Index", 1, 1, Int64.Type)
in
#"Added Index1"

[Anonymous]

Hi @Anonymous ,

Please try this:

let
    Source = Excel.Workbook(File.Contents("C:\Users\TEST.xlsx"), null, true),
    Tabelle1_Sheet = Source{[Item="Tabelle1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Tabelle1_Sheet,{{"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1", "Column2"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filled Down", "Column2", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column2.1", "Column2.2"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Split Column by Delimiter", {{"Column1", type text}}, "en-US"),{"Column1", "Column2.1", "Column3"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Merged"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Column2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Column4", Int64.Type}}),
    #"Transposed Table1" = Table.Transpose(#"Changed Type2"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true])
in
    #"Promoted Headers"

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Anonymous]

Thank you let me try with your solution.

Can you kindly also see my related question below?

https://community.fabric.microsoft.com/t5/Desktop/Get-the-values-from-merged-and-multi-layered-colum...