Forum Discussion

Mickeybo's avatar
Mickeybo
Frequent Visitor
5 years ago
Solved

Creating Index by Row Value

I am importing txt files to Power BI. The file structure is  |-> Sales Details Header Sales Details record |-> Sales Details Footer   I am trying to add an index to these "Sales Detail Heade...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    Here is one way to do it in the query editor.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY/LCoNADEV/RWbtIs9JshSc0oIgVHfi//9GLd0oTZvtfZzcbSvLMLWlG9s6PKalu7dhbM/SF7QArYQAbJXL3l+db4ciuqdKVQI8Kbd5Xj+tLsZc5St1Jjsro5KpoOTkCmqU90ONgPq3344L1nCVyP83AqGczKC5ouKOeQZJI+eA/NhhB0ZZy76/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Header", each if Text.Contains([Column1], "HEADER") then [Column2] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Header"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Header"}, {{"AllRows", each _, type table [Column1=nullable text, Column2=nullable text, Header=text]}}),
    AddHeaderIndex = Table.AddIndexColumn(#"Grouped Rows", "HeaderIndex", 1, 1, Int64.Type),
    AddDetailIndex = Table.TransformColumns(AddHeaderIndex, {{"AllRows", each Table.AddIndexColumn(_,"preDetailIndex",0,1), type table }}),
    #"Expanded AllRows" = Table.ExpandTableColumn(AddDetailIndex, "AllRows", {"Column1", "Column2", "preDetailIndex"}, {"Column1", "Column2", "preDetailIndex"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded AllRows", "DetailIndex", each if Text.Contains([Column1], "HEADER") or Text.Contains([Column1], "FOOTER") then null else [preDetailIndex]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"preDetailIndex"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Header", type text}, {"Column1", type text}, {"Column2", type text}, {"HeaderIndex", type text}, {"DetailIndex", type text}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "NewIndex", each if [DetailIndex] <> null then Text.PadStart([HeaderIndex], 4, "0") & "-" & Text.PadStart([DetailIndex], 4, "0") else Text.PadStart([HeaderIndex], 4, "0"), type text)
in
    #"Added Custom2"

     

    Regards,

    Pat